price field value varies function of quantity field same record

[Guest]

My product has varying price/unit depending on quantity (i.e 1-3 units =
50$/unit, 4-10 units= 45$/unit etc ). Do I need a new record in product table
for each price structure or is there a way of having a range of numbers in a
table field. Then when client chooses quantity I want to select the correct
price in a query. (i.e. select 5 units, so price is 5 x 45$). That part isnt
difficult i think.
Any help would be positive karma.

 

[Allen Browne]

One product can have many prices (depending on quantity), so you have a
one-to-many relation between parts and prices.

You need this ProductPrice table in addition to your Product table:
ProductID Number relates to Product.ProductID
MinQty Number minimum quantity to get this price
PriceEachEx Curency price for this product at this quantity, ex
tax.

You can then use Tom Ellison's suggestions on how to look up the price for
the product:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

Thanks for your very swift and well explained help. I guessed I would need
this second table but then couldn't get my brain to think what to do next.