Selecting a Price from a Drop Down in Query

[henryihunter1954]

First, I'd like to thank those that have previously helped me in the past.
This is one of the best groups to come for if a person has questions about MS Access....

I have a:
Items Table
Items Table has Item ID, Name, Cost, and Category.

I am going to use this table when the Date Entry person keys in an order.
I've set up a query to enter the information in the Orders Form.
So far, all has worked well until I realized that each Item has the option of prices.
Example: High - $100; Mid - $75; Low - $60

I want the Data Entry person to select the proper cost after the Item has met the Item ID criteria, Name, Description, etc and also the Category.

Thanking you all in advance for the great assistance.

 

[June7]

So what exactly are you wanting - a second combobox that shows only the prices for item selected in first combobox? This is called cascading combobox - a very common topic.

Do you have fields High, Mid, Low? Or is each price level a separate record?

 

[henryihunter1954]

June 7:

Thanks for responding.

I do have a first drop down that will allow the Data Entry person to select the Item. Once the Item is selected, the row is populated with info...Name, Description, Category, etc.

When I initially set the Item table up, I had a Cost field. (Naturally, it was Currency). I realized that I needed to have a drop down for Cost. The Cost could be 1 of 3 possibilities.

You are correct about having cascading drop-down...the 2nd field (Item Cost or what ever I'm going to call it) will be based on the Item and the Category.

At this point, I've created an Item Cost Table, that has the 3 prices in it.
I am assuming that I need a foreign key in the Item Table that will refer to the Item Cost.

Again, thanks for your assistance and very quick response.

 

[henryihunter1954]

Thank you Pat Hartman. (I believe you have helped me also in the past.)

I want to be sure I have this correct in what you are explaining to me:

I need to have a foreign key in the Item Cost Table. That foreign key will be the Item ID from the Item Table.

Is this correct?

 

[AccessBlaster]

It sounds like you want a table to holds prices? What happens when the prices changes?

 

[henryihunter1954]

I have a form for each Item Cost. I can pull each one up and change the prices or price of each level

 

[henryihunter1954]

Let me better explain.

I have the Item with the other fields. When I go to that Edit - View Item,
I can see all those fields and edit them.

Below that, I have a table that has Low, Mid, High with each price beside it.
I can enter as many different rows but I only want those 3.

 

[June7]

Structure still not clear to me.

tblItems
ItemPK

tblItemPrices
ItemPricePK
ItemFK
PriceType (LOW, MID, HIGH)
Price

tblOrders

tblOrderDetails