Microsoft Access: how to select the right price for each customer type?

You tProduct table is not normalized, and you should fix that before proceeding. You should never have columns named PriceA, PriceB, etc. It would be okay to have columns named SellingPrice (i.e. the price at which an item is sold) and PurchasePrice (i.e. the price at which you purchase the item), but have 3 columns to store the same thing is a sign of an improperly designed database.

As mentioned earlier, you should have a table that stores your Customer Prices, and use that to determine the price you'll charge for a Customer. Your tProduct table could contain a DefaultPrice column (i.e. the price you will sell if for if there is no custom pricing), but if you need to apply specific pricing for specific Customers, then you'll need to have a Many-to-Many table to store those prices, as I mentioned above.

If pricing is dependent on the CustomerType, then your new tProductPrice table would look like this:

tProductPrice

ProductPriceID

ProductID

CustomerType

ProductPrice

Each of your Products would have a single row in that table which would relate the Product to a specific CustomerType, and would provide you with the ProductPrice for the Product. To get the correct price you'd query the tProductPrice table with the ProductID and CustomerType:

SELECT ProductPrice FROM tProductPrice WHERE CustomerType='A' AND ProductID='123456'

You could also include tProductPrice in any query that is needed to retrieve that data:

SELECT tCustomer.Customer, tPRoduct.Product, tProductPrice.ProductPrice, tProduct.DefaultPrice FROM tProduct INNER JOIN tCustomer ON tProduct.CustomerID=tCustomer.CustomerID LEFT JOIN tProductPrice ON tProduct.ProductID=tProductPrice.ProductID AND tCustomer.CustomerID=tProductPrice.CustomerID

Of course, those table and column names are just ones I made up. In order for the query to work you'd have to change them to match your own.

HI Scott,

Thank you for your help.

I will try and let you know if I got it right!

Isidro2019

Can you show your Product table? If your storing all those prices in the Product table, then you're probably going to need to change that.

In many cases, you'd have a single price in the table (the DefaultPrice), and have a different table to store your different prices for each Customer:

tCustomer_Prices

---

CustomerID

ProductID

CustomerPrice

You'd simply include that table in your query (normally with a LEFT join, in case a particular Customer doesn't have an entry in that table), and then use logic to determine if you should use the DefaultPrice, or the CustomerPrice.

Thank you for your help!

my tProduct is

ProductId

Description

Cost (Default price)

PriceA  <markup diferent>

PriceB

PriceC

tOrdeLine

OrderId

ProductId

Quantity

SalePrice

TotalLine

Regards

Isidro2019

Can you show your Product table? If your storing all those prices in the Product table, then you're probably going to need to change that.

In many cases, you'd have a single price in the table (the DefaultPrice), and have a different table to store your different prices for each Customer:

tCustomer_Prices

CustomerID

ProductID

CustomerPrice

You'd simply include that table in your query (normally with a LEFT join, in case a particular Customer doesn't have an entry in that table), and then use logic to determine if you should use the DefaultPrice, or the CustomerPrice.