Products with multiple price levels

[Guest]

Hi
I'm looking for help with the following;
Concerning Access article Number (207763)

Specifically Method #2: Compute the Calculated Expression in a Query or in a
View

This example only calculates the “extended price†based on one “unit priceâ€.
What if the Item had three price Fields; Each price, Case price, Quantity
price and also two quantity fields; Case quantity and Master Quantity.

The final "unit price" charged would de based on the Quantity purchased and
depending on the quantity one of the three price levels mentioned.

Ex: ProductId:Apples , Each Price=1.95 , Case Price=1.50 , Quantity
Price=1.00
Case Quantity=12, Master Quantity=100
If you purchase 1 - 11 Unit Price=1.95
12 - 99 Unit Price=1.50
100 + Unit Price=1.00
Can you offer help on how to change this query to produce this result?

Thanks

 

[Allen Browne]

Hi Jody. Several possible approaches here.

1. Separate Products
================
A common one would be to treat "Apple", "Case of Apples", and "Crate of
Apples" as 3 different products, so the Product table contains:
ProductID primary key
ProductName "Apple" or "Case of apples", etc.
Size Number
Unit "Kg", "Litre", "Count", etc.
ProductPrice Currency

This allows you to have a product that is a box of 100 apples @ $110 for the
product, and product that is a box of 50kg of apples @ $30 for the box
(regardless of count), and so on. It also makes it easy for your customers
to order the product in a particular way.

2. Tiered Pricing
=============
If your product is not actually packaged like that, and you just want a
tiered price scale, you could have a price table like this:
ProductID which product this price applies to
MinQuantity the minimum quantity to order to get this price
UnitPrice the price each for this product from this qty upwards.
For any particular order, you can then get the price as:
ELookup("UnitPrice", "PriceTable", "(ProductID = " & [ProductID] & ")
AND (MinQuantity <= " & [Quantity] & ")", "MinQuantity DESC")
where ELookup() is the function from this web page:
http://allenbrowne.com/ser-42.html

Tom Ellison has a more detailed discussion on handling price ranges here:
Lookup a value in a range
at:
http://allenbrowne.com/ser-58.html

3. Embedded Products
==================
Another possibility is that you need to handle products inside other
products, e.g. you might sell:
- applies, and
- boxes of apples, and
- crates of boxes of apples, and
- containers of crates of boxes of apples
and so on. If you then need to be able to resolve these down to get a total
count of the base product (apples) in all the embedded packaging forms, life
gets interesting. For an introduction to this idea, see:
Bill Of Materials
at:
http://www.mvps.org/access/modules/mdl0027.htm

Chances are only one of those ideas was what you had in mind, but hopefully
it gives you a useful lead.