By Richard Rost 7 years ago
Today's Question of the Day comes from Colin M. He asks:
Reply from Richard Rost:
Interesting problem. I can't think of an easy NON-programming solution for this, so you'll need a little VBA or a macro when you add a product to your purchase order.
You will need to have a separate table to track pricing for each of your product price breaks. Let's call it ProductPriceT. It would have the ProductID (foreign key) and then a MinQty and Price. So for example, your table would look like:
1, 1, $1.99
1, 10, $1.59
1, 50, $0.99
2, 1, $200
2, 100, $150
This says that Product 1 would cost $1.99 in quantity 1-9, $1.59 in quantity 10-49, and $0.99 from 50 and up.
Product 2 would cost $200 for quantity 1 to 99, and $150 for quantity 100 and up.
Now, when you add a product to your purchase order, you'll have to look up the appropriate price from this table using a combination of DMAX and DLOOKUP. You'll need DMAX to find the largest price break equal to or less than the quantity you're ordering, and then DLOOKUP to pull that price. So for example:
LargestPriceBreak = DMAX("MinQty","ProductPriceT","MinQty<=" & QuantityOnOrder & " AND ProductID=" & ProductID)
This will find the largest price break for the product LESS THAN the quantity your ordered, for that particular product. So if the quantity is 45 and product ID is 1, it should return 10 (largest price break of 45 or LOWER). Now that you have that, you can return the unit cost:
UnitCost = DLOOKUP("Price","ProductPriceT","ProductID=" & ProductID & " AND MinQty=" & LargestPriceBreak
And there you have it. You now have the appropriate unit cost to assign to that line item of your purchase order. You can throw it in your button event that adds the product (like I did in Access Expert 22, Lesson 3).
Very good question. I'm going to make this the Question of the Day and possibly add it to a future class.