Sales order (northwind) with Multiple prices for each product

[Guest]

HELP HELP HELP! Based on the northwind dbase, I need to figure out how to
modify / insert the unit price into the order details subform
(orderdetails.unitprice).
the problem is that there are different prices for the same product based on
different customers - some customers get a break that varies by prodcut.

I have added a new field for each customer in customers table: a pricing
level e.g. customers.pricing (=a,b,c). Each product has a productID,
partnumber in table: Products. I have also created a pricinglevels table
with the following fields:
[pricinglevelID, part number, pricing, unitprice] with pricing=a,b or c

Customer Product Pricing
Levels
------------- ------------------
---------------------------
customerid productid
pricinglevelid
pricing productnumber productnumber
addresses, etc productname pricing

unitprice

So I can't pull the unit price from the products table (products.unitprice)
directly as in the sample db. I need to match up the correct product,
prcinglevel and pull that into the order detail's unitprice field.

I do not need to keep historical or date information on pricing!

 

[Michel Walsh]

Hi,


With a table of prices like:


ProductID, CustomerID, QtyUpTo, UnitPrice

1010, 1111, 10, 1.25$
1010, 1111, 50, 1.15$
1010, 1111, 999, 1.02$




it could be to use a query:


SELECT a.Customer, a.ProductID, a.Qty, MIN(b.UnitPrice)
FROM myTable As a LEFT JOIN tableOfPrices AS b
ON a.ProductID=b.ProductID
AND a.CustomerID = b.CustomerID
AND a.Qty <= b.QtyUpTo
GROUP BY a.Customer, a.ProductID, a.Qty





The 'simplicity' of the SQL statement is due to the fact that the table of
price has just on set of information "by row", and it is also quite
flexible, since you can define as many price category as you want.




Hoping it may help,
Vanderghast, Access MVP

HELP HELP HELP! Based on the northwind dbase, I need to figure out how to
modify / insert the unit price into the order details subform
(orderdetails.unitprice).
the problem is that there are different prices for the same product based
on
different customers - some customers get a break that varies by prodcut.

I have added a new field for each customer in customers table: a pricing
level e.g. customers.pricing (=a,b,c). Each product has a productID,
partnumber in table: Products. I have also created a pricinglevels table
with the following fields:
[pricinglevelID, part number, pricing, unitprice] with pricing=a,b or c

Customer Product
Pricing
Levels
------------- ------------------
---------------------------
customerid productid
pricinglevelid
pricing productnumber
productnumber
addresses, etc productname pricing

unitprice

So I can't pull the unit price from the products table
(products.unitprice)
directly as in the sample db. I need to match up the correct product,
prcinglevel and pull that into the order detail's unitprice field.

I do not need to keep historical or date information on pricing!

Dave