Look Up Price From Another Table On Form

Jun 13, 2006

Ok, having a very beginer problem here (I Think)

Here are my relevant tables:
I have a table called Items which has itemCode (primary key), itemDescription, and price.
I have a table called bidItems which has bidCode (primary key), itemCode (primarykey), and some other info
I have a table called Bids witch has a bidCode (primary key), and a bidDescription

Here is a form that I use to enter data into these tables and (want) to view prices and totals:
I made a form based on the Bid table with a continuose subform based on the bidItems table, with combo boxes that lets you select Items by itemDescription (then it stores itemCode automatically with the correct bidCode)

What I can't figure out how to do is get each item's price to automatically display on the form (remember price is in Items table, not bidItems). I've tried a number of things that havn't worked out.... any ideas. Then, ideally I would like the form to display the total of all the item prices. (if you have a pointer on that as well it would be much appreciated)

Thanks

View Replies


ADVERTISEMENT

Reports :: Populate Table With Price From DLookup In Form To Total In Report

Apr 25, 2013

I am working with a database that I downloaded and am trying to modify to fit my needs.

This is an inventory database. The products table contains a description and pricing. I want the description and pricing to populate in the Purchase Order form, so I added Dlookup fields in the Purchase Order form. I was happy.

However, the pricing information is not populating to my Inventory Transactions Table from the Purchase Order form by way of this Dlookup feature, and therefore will not show on my report, and in turn does not show in my Total of my Purchase Order report.

As a work around, I tried creating a calculation in the purchase order report, of =[UnitsOrdered]*[Products.UnitPrice], and the pricing totals show fine on my report, but the subtotal doesn't work.

I was unable to upload my file...so a few notes of info...

There are no queries set up in the database for this report.

I had tried a sorting grouping thing (in the Report) by Subtotal, but now can't get rid of it.

When I show the field list for the report, across the top of the window reads:

SELECT DISTINCTROW Employees.*, Products.*, [Inventory Transactions].*, [Purchase ORders].*, Suppliers.*, nz([Inventory Transact

Looks like it runs out of space

I am trying to attach a couple of images to support my comments.

Since this issue crosses both reports and forms (and tables!), I am not sure where to properly post. The end result I am looking for is on my report.

I am using Access 2003...

View 1 Replies View Related

Queries :: Item Without Price Or Duplicate Price

Jul 29, 2013

I have a table called Books, in that table there is 4 columns ChapterName, Auther, ITEM, Price.

Each book has a item number, and each book has a few records with the same data, just the first column is different where its the ChapterName, each book has a price, but only once, meaning in the first record of each book it will be a price in the column price

Now I want a Query where i can get which book dont have a price at all, and which book has more than once a price, how can i do that?

View 1 Replies View Related

Calculating Quantity * Price In Table PLEASE HELP

Oct 11, 2006

I am having trouble calculating a field in a table and on a form.
I have the following Tables
CUSTOMER: Customer No, Name, Address, City, State, Zip, Discount, Phone, Fax, Contact Person.
ORDER: Customer No, Order No, Order-Date, Delivery-Date.
ORDER LINE: Order No, Product No, Quantity Ordered,
line-item-cost
PRODUCT: Product No, Description, Price, Product Image.

I need to calculate the line-item cost and need it to update every time the user enters a new Quantity ordered on the ORDER LINE form.
I know its (Quantity Ordered*Price)-(Price*Discount) and I did this is a query and it worked but I need it to update in the form and update in the table not the query. I have tried everything (Macros, Queries, and Formulas) and I just can not get it to work.
Does anyone know how to do this?
Thanks

View 1 Replies View Related

Normalize My Table - Track Price Levels

Aug 22, 2005

i would like to have a table where i can track price changes, however i don't want this to based on an inventory order.

i have a table which has the amount of hours to be charged. i then have another table which has the price per hour charged. the price keeps on changing. both tables have the date. tableHours has the date of occurance and TablePrice has the date when the price was last updated.

i need to know how to structure the TablePrice. currently it is set as BillableHourType, Rate, EffectiveDate. If this is correct, i don't know how to run a query.

i need to multiply Hour*Price, criteria: Effective Date must be most recent date as of Date of Occurance.

All Help will be appreciated.

I posted this post on the Microsoft site and i got no good responses, i am new to this site, but if it is a problem of reposting, please let me know. i will not do it again.

thanks,

sam

View 7 Replies View Related

Calculating Quantity * Price In Table PLEASE HELP Wont Show Up

Oct 11, 2006

When I use the code given to me the program complies but it does not show the new total price in the for box
dim a, b, c
a = me.[your quantity textbox]
b = me.[your price textbox]
c = a*b
me.[your total textbox] = c
This is the code I used and my total box is called (line_item_cost)
is there anything that I am doing wrong?

View 5 Replies View Related

Tables :: How To Design Price List Table (Lookup)

Mar 25, 2013

I have to design a price list table which is currently done in Excel.

The table looks like this :

[Headers]

Product Group | Layers | (this are quantity bands) 0-100 | 101-500 | 501-1000| etc
Test1 | 2 | £10(normal) £9(special)

The quantity band currently is fixed to 5 bands but would need to be flexible. There are also 2 prices for each of the quantity band (normal/special)

At the moment my table design looks like this:

ID
fkSupplierID
fkProductGroupID
txtLayers (value list)
intMinQty
intMaxQty
curNormalPrice
curSpecialPrice

This works quite well with the query to return price based on product group, layer and order qty. However I am not very sure if this is the best way to design this. I am just thinking about maintenance - for example when the supplier puts in a price change or when the quantity band changes. The current format (quantity band) is based on a major supplier but in the future we would like to adopt this for any supplier.

View 6 Replies View Related

Forms :: Purchase Order Form - Calculate Auto Total Price

May 20, 2014

I need to make an order purchase form like i have customer table and product table, i want to make a form which creates order for a specific customer and I can add as much items as i want and than calculate auto the total price, and an option to make a report for it.

View 5 Replies View Related

Whats Your Price?

Jan 2, 2006

After being a member on this site for a while now I would like to get some opinions on "cost of application development" I know the forum covers a wide geographic area and the members cover a wide spectrum of degrees of expertise on Access and application development. So the question is this..What would your rates be and how would you determine your charges for developing Access applications for a business?PS... Please include your location if not in your profile.

View 13 Replies View Related

2 Price Fields

Jul 9, 2007

Hi

I have a problem which hope will have a simple solution, which has been
driving me mad all day

I have a client who has two prices for the same service depending on speed
of turnaround
These prices are entered into the Client table seperately using the CDetails form.
I then wish to use this information using a combo box for selection within
another form called SDetails.However I can only get the combo box within
SDetails to put one of the two prices rather than either of the two choices,
because of the bound column within the combo.
The combo when pulled down looks something like this

Price 1 Price 2 Client Name

I would prefer for

Price 1
Price 2

so that I have the choice of the price I want to choose.

Having spent time and many brain cells, I have ground myself to a stop.
HELP!!!!!! if only for the sake of my sanity

View 1 Replies View Related

Price Lookup

Oct 13, 2007

I am creating a db that handles orders.

My problem is that the products being sold are so at different prices depending on the customer. (e.g I am selling 100 burgers to Mcdonalds for £50 whereas to Burger King I am selling them for £35).

Therefore I have split my 'Products' table into two. A 'Products' table which contains the product name and category. And a second 'Prices' table which contains the Product name, Customer and the price.

I have setup a relationship between the two tables based on product name

How to I go about looking up the different prices depending on which customer I have placed an order for?

can it be done in a table or do I need to create a query to sort it all out.

View 8 Replies View Related

Nth Smallest Price By Row

May 15, 2007

I am in need of some assistance. I've been looking on here for an answer, but can only find solution that would give me a by column lowest price, and I need to compare the prices by row. What I have is a table of vendor prices for part numbers we sell. What I want to do is compare all the prices the vendors for each part number and return the lowest and 2nd lowest fields in a query. Here is an example...

Part # vendor 1 vendor 2 vendor 3 Lowest Vendor Price Low Vendor #
10526 .75 .97 .67 .67 3
10527 .60 .82 .85 .60 1


Thanks for any ideas....

View 4 Replies View Related

Adding Price History

Sep 11, 2005

ok i didnt know what section to put this in so i put it here. what i wanna do is add a price change history to my database. for example is the price of an item is changed in my database i want to be able to see what the previous price was and when it was last changed, either in forms of reports doesnt matter i just need somewhere to view this info. problem is i have no idea where to start.

any help provided is muchly appreciated thanks.

View 4 Replies View Related

How To Create A Price List

Jan 18, 2005

Hi all,

Before I start, please know that I am what you call a newbie to a certain extent even though I created in the last 3 years a very complex and efficient database for my business.

This is going to be hard to explain, but I'll try.

The time came to stop creating the Price List in Excel and copy and paste in Access.(mainly to make it easy for other person to maintain and understand)

I know more or less the tables that will need to be created.

My problem is how to store calculated fields in a table (I know I really shouldn't, but how else can I accomplish this), since all the costs and selling prices are the result of complex formulas.

If this information is not enough to understand my question please let me know.

Thank you for any help.
Emilio

View 10 Replies View Related

Tables For Price List?

Jan 22, 2005

Hi all,

I started a new thread, because it is a new subject even though is related to my Price List thread, I hope is OK.

If someone can please take a look at my attached Excel Price List (particulary the factors sheet) and give me an opinion of how my tables should be created.

I cleaned the Price List was too big to attach and it will be easier to be understood, in its entirety is kind of all over the place, exactly why I need to make simpler in Access for another user to update if I am not around.

I am below including what I think the tables should include, but not sure exactly how they should be, please be reminded that I am un unexperienced newbie.
----------------------------------------------------------------------

tblSuppliers

SuppliersID
SupplierName
SupplierCode

tblMarkup

MarkupID
MarkupName
MarkupAmount


tblTaxes

TaxID ------Do I need This?
TaxName
TaxAmount

tblExtras

ExtrasID
ExtrasName
ExtrasPrice
SupplierID

tblDiscounts

DiscountID
DiscountName
DiscountAmount
SupplierID
----------------------------------------------------------------------

Thanks for any help,
Emilio

View 6 Replies View Related

Date To Price Relationship Help

Nov 2, 2005

I am building a database in Access which I would like to eventually link to my website in order to sell hotel bookings online. I believe I have set all the fields up properly but when it comes time to set the price I'm lost. Each hotel has about 3-5 different prices throughout the year depending on the date. How can I design a table(s) to accommodate this? Any help is appreciated.

View 3 Replies View Related

Extended Price Subform

Feb 5, 2005

I am stuck for a calculation expression for 'extended price' which can be seen in the attached screenshot.

I want to times item price by quantity and deduct the discount.

Thanks. :)

View 4 Replies View Related

Two Field For The Price Of One Combo

Sep 5, 2005

I have a combo box which looks up vessel names, it stores the Vessel ID in a field [Vessel ID]. This all works fine, no problem, easy. However as part of the vessel details I also need to know the service the vessel is providing “transhipment” or “direct”. If it is a “transhipment” service I need to show a second Combo box with the on carriage vessels. That’s also easy, I just hide the second combo box and only show it depending on the service status of the vessel.

Now I figure I just need to have a field [Vessel Status] which is updated after the first combo box is updated and then depending on the value show or hide the additional combo box. The problem I’m having is updating the [Vessel Status] field once the first combo box has been update.

View 1 Replies View Related

Discount Price Query

May 27, 2006

For an assignment I am required to do the following:

Quote: Use an expression (in field name of design view by using the build button) to create a new field (in Design View of a Query) called "Discount Price" in table "Stock" which is a 25% discount for all stock items from the new inflated price in query B (use expression builder if needed and create it the field name of a query; it will not appear in the table if you try to open the table).

So sorry to bug again. Any ideas or processes on how to go about this would be welcomed

View 2 Replies View Related

Last Price Of Month Query?

Apr 1, 2015

We have this database:

It has Symbol on the first column, Date (daily on trading days) on the second and Close on the third.

We want a query that shows the last CLOSE of the month for each symbol and sum the volume of the month.

The query table would be like this:

Symbol
Date (last date of the month)
Close (last date of the month)
Volume (sum of whole month)

Here's the sample file:

Sample.accdb

View 9 Replies View Related

Obtaining Latest Price Data

Jul 23, 2007

Hi,

I have a table containing the following fields;

ProductID (UniqueID)
Date
Price

Each day some, but not all, of the products in this table have their prices updated.

When I write a query how can I ensure that the latest price is recalled ?

Any tips greatfully received.

View 2 Replies View Related

Price Calculation In Access Like Excel

Dec 11, 2007

Hi all,

Help me solved my problem with price calculation in Access.

Please kindly view below excel file.

http://www.hwakeat.com/templates/AT05HKGP01/pdf/Products.xls


I like to do formula price calculation in Access like my sample excel file. How many table should I create & How to build query? Whenever exchange rate change or MarkUp price rate change, I only want to type once & it will affect the whole access.

I have tried so many ways in Access but it doesn’t work like my sample excel file.

Please Help me!!

View 1 Replies View Related

Assign A Cost/price Value Toodrop Down Box

Jan 17, 2008

Hi all,

Might be in the wrong place but didnt know it it needed to be in query, form or vb sorry admin.

I need help, what i need is a way of assigning a price too a drop down menu.

Basically its a Form with a number of drop down menus where you can select different varibles colour, finish for a certain product, but theres no method to generate a total cost of selecting different variables.

Do you know of a soloution becuase ive exhausted my capacity!!

Thanks

View 5 Replies View Related

Import Stockquote/ticker Price ....

Sep 15, 2005

how to import stockquote/ticker price directly into a access table, also
if with throu excel or an excel interfcae?

View 3 Replies View Related

Newby Link Price Fields

Feb 20, 2006

So I have a table "InventoryTracker", with one field lookup set to list (drop down) and the source is another table "inventory", field "Item". This works good and all the items that I enter in the "inventory" table are available from the "InventoryTracker" table. Both "inventory" and "InventoryTracker" have a field called price.

Heres what I want to have happen.
When I select a item from the drop down list menue in "InventoryTracker" it automatically fills in the "InventoryTracker" price based on the price from the "inventory" table (for the item with the same name, the one I just selected). This has to be fairly straitforward, I can do it in excell, but not acess.

Can anyone help a newby out?:rolleyes:

View 2 Replies View Related

Price Based On Latest Date

Oct 28, 2004

Hi guys

I am trying to create a simple database to keep track of computer components that I order for custom PCs. The data is fairly basic Quantity, Description, Stock Code, Delivery Status, Cost, Cost+VAT, Date Delivered. The list is currently 500+ rows of data. I simply cut and paste this info directly from my online invoices.

What I want is to be able find the the last price paid for an item (often the same items have different prices week by week) Eventually I would like to be able to create a rough quote using the latest prices (plus mark up) and also view a price history.

I would also like to include a few pictures as URL links to the suppliers website ????

At present the data is in Excel and linked to a single table in Access (but this can be changed). I realise that I have to group the records somehow but cannot get it to work. At present it lists every record either in date order or product order.

The stock code should not change, but product descriptions do. So any links to pics would be based on stock codes

Someone please tell me how to display the full list of products but only the latest price/date

If you think the current format is rubbish then feel free to suggest an alternative. I much prefer Excel but have never really got to grips with Access (used to be a vba programmer with Excel). To link to pictures and hide data that customers might see Access seems the obvious choice. So I try again!!!!!

Thanks

Steve

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved