Thanks in advance
How do you currently determine what that price is for each customer? A percentage? A specific price? Based on quantity? Is each product a different pricing structure? How many products are you dealing with? Information is key to figuring out what may be needed.
Thanks for your response Ratslinger. I appreciate I was a little vague. I have around 150 products and 50 or so regular customers. 80% of the prices are standard across the board but the others are specific prices to each customer (most individually negotiated to beat existing suppliers / encourage repeat orders).
With 150 products and 80% standard prices, a product line for each customer is unreasonable. If a price changes, you would need to change it in 50 places.
With a price table containing the product number (or name - unique identifier) and a customer number (with say customer #999999 being the standard price and non 999999 being specific to a customer it can be done with some code in a macro. Can’t see any custom pricing without some coding. Mostly done with SQL like (pseudo code):
SQL = Select Product, Price From tableX where Product = XXXXX and Cust# = XXXXXX
Execute SQL statement
Check return
If return empty Then
SQL = Select Product, Price From tableX where Product = XXXXX and Cust# = 999999
Execute SQL statement
Check return
If return empty Then
Display INVALID PRODUCT NUMBER
Exit Routine
EndIf
EndIF
Retrieve Product and Price.
The first SQL statement checks if there is a special price. If not then get the standard price. If that is not found, it is an invalid product number.
To me, keeping special pricing in a separate table would be more confusing for managing purposes. Multiple places to look and still requires some coding.