Build "between" parameter query programatically

[Robin S.]

I have a table (tblDiscounts) which is a discount structure. Depending
on the situation, that table can have one or more records.

Structure:

Minimum Maximum Multiplier
$0 $4.99 .95
$5.00 $9.99 .9
$10.00 $50,000.00 .85

I need to apply this discount structure to a table (tblProducts) with
products to calculate a selling price from list.

Structure:

Product No List price
001 $3.45
002 $7.27
003 $13.75

Basically I need to create a SELECT (and later an UPDATE) query which
evaluates the list price of each product (tblProducts), and applies
the appropriate multiplier from tblDiscounts.

My problem is that the discount structure may have one record (like,
$0 to $100,000, multiply by 0.7) or it may have several records (as
above).

I'm looking for an efficient way to create the SQL statement for the
query. It will be created as required for each price list. I didn't
want to get into nested IIF arguments as it seems clunky to do
programatically.

Thanks for any key words or small code examples.

Regards,

Robin

 

[Michel Walsh]

SELECT orders.*, discounts.multiplier
FROM orders INNER JOIN discounts
ON orders.price >= discounts.minimum
AND orders.price < discounts.maximum


Note: - you should not have overlap in the ranges {minimum, maximum}, else
the join could create TWO records in the result (or one per range into which
it falls)
- use $5.00 as maximum and keep the < , or keep the $4.99 and use <=
in
orders.price < discounts.maximum



Vanderghast, Access MVP

 

[Robin S.]

SELECT orders.*, discounts.multiplier
FROM orders INNER JOIN discounts
ON orders.price >= discounts.minimum
AND orders.price < discounts.maximum

Note: - you should not have overlap in the ranges {minimum, maximum}, else
the join could create TWO records in the result (or one per range into which
it falls)
- use $5.00 as maximum and keep the < , or keep the $4.99 and use <=
in
orders.price < discounts.maximum

Vanderghast, Access MVP

Vanderghast,

Thanks for your suggestion, and your time. It worked perfectly.

Regards,

Robin