I am trying to figure out the best way to store information. What I have is product that are priced differently at different quantity levels. I am going to want to access the information even in between the levels. In other words, let's say I'm selling frisbees and the pricing structure is:

10 frisbees for $5 each
50 frisbees for $3.50 each
100 frisbees for $3 each
500 frisbees for $2 each

What I will want is for Access to check how much 70 frisbees cost (round down to $3.50 each). The quantities are not the same for every product.

Is there a way I can store only the quantity numbers above and have a query access the information? Or do I need the "max" number for each level, i.e.

10-49 frisbees for $5 each
50-99 frisbees for $3.50 each
100-499 frisbees for $3 each
500- frisbees for $2 each

and then find >first number and <second number ?

If you were to store your items/costs in tblMyProducts with fields:
ProductID: Autonumber
Product: Text
Qty: Number
Price: Currency

Example:
1 Frisbee 10 $5.00
2 Frisbee 50 $3.50

…etc..

This totals query should give you approximately what you've described:

PARAMETERS [Enter Product] Text, [Enter amt] Short;
SELECT TOP 1 tblMyProducts.Product, [Enter amt] AS Amt, First(tblMyProducts.Qty) AS FirstOfQty, tblMyProducts.Price, [Enter amt]*[Price] AS TotCost
FROM tblMyProducts
GROUP BY tblMyProducts.Product, [Enter amt], tblMyProducts.Price
HAVING (((tblMyProducts.Product)=[Enter Product]) AND ((First(tblMyProducts.Qty))<=[Enter Amt]))
ORDER BY tblMyProducts.Price;

I would use both min and max numbers as part of the key to the price table -

tblPrice
ProductId (primary key fld1)
MinQty (primary key fld2)
MaxQty (primary key fld3)
UnitPrice

Select T.*, P.UnitPrice from YourTable as T Inner Join tblPrice as P On T.ProductId = P.ProductId
Where T.Qty Between P.MinQty And P.MaxQty;


You should also include the 1-9 price in this table to simplify your processing. Therefore, the price for EVERY item whether it has volume discounts or not is included in this table.

Thank you both for your thoughts. I was leaning towards Pat's way anyway (and I am a big Pat Hartman fan! http://www.access-programmers.co.uk/ubb/smile.gif )

One more question: how would I deal with the "infinite", i.e. the topmost price (the greatest quantity with a price breakdown - 500 in my example) would have a minimum but no maximum. I suppose I could just choose an outrageous number, but is there another way?

There is always -
Where (T.Qty Between P.MinQty And P.MaxQty) OR (T.Qty >= P.MinQty And P.MaxQty Is Null);

Of course if you allow nulls for MaxQty, you'll have to limit the key to ProductId and MinQty.

If you are using a form to allow users to maintain the ranges, you should verify that the ranges do not overlap or the above query will not work.