Proper structure for quantities

[shacket]

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 ?

 

[raskew]

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;

 

[shacket]

Thank you both for your thoughts. I was leaning towards Pat's way anyway (and I am a big Pat Hartman fan!

)

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?