Looking for multiple values in other tables (Newby!)

[redj]

Hi all.



Bit of newby question here.

I want to create a simple db which records sales. I have a sales table and a customer table and I want a field in my sales table to automatically look up the price of a product when the product is entered.

I know I need a products table however it is complicated by the fact that each customer pays a different price for each product. There is only 4 products so at the moment my customer table has – CustomerID then then 4 fields for the price for each product . Im not sure how to go about having a different price for each customer and how to have the sales table look for these prices when the product is entered.

Can someone point me in the right direction with this?

Many thanks

[alansidman]

This is more a business decision versus a programming decision. In the future, how will you determine or establish pricing for new customers? Will you establish a business model that arbitrarily negotiates a different price for every customer or will you have 4 or 5 pricing levels and assign a specific price level for a customer.
In the interim, I see only one way to do this. Set a price level for every customer for every product in a separate table.

Your table would look something like this:

ProdPriceTable
--------------
ProdPriceID (PK)
CustID (FK)
ProdID (FK)
Price

I would then have a combo box for the product and in an adjacent text box I would link the price to the table above based upon the value in the combobox and the custID
Alan

[redj]

I completely agree with what you say. For the purposes of simplification- I used the term sales- however my database actually looks at prices we are paying for various services used with established partners. So the prices do differ significantly and there is nothing I can do about it!

What I want to establish is a price matrix which can then be referenced by other tables.