Best way to lookup a value from multiple ranges
I need to determine what range a value fits in order to determine the appropriate discount. Just trying to determine the best most efficient way to do this in Access (2016). Here is a simplified table:
My first thought is to loop through a recordset to evaluate each record for >= FROM$ and =< TO$ until a range match is found, then grab the discount rate. Would there be a more efficient way to do this with an SQL statement?
3 answers

We can try using
BETWEEN
here:SELECT MAX(IIF(price BETWEEN FROM$ AND TO$, Discount, NULL)) AS Discount FROM yourTable;
We might more typically expect that your table of discounts would store information for multiple groups, e.g. products. In that case, we could write:
SELECT product, MAX(IIF(price BETWEEN FROM$ AND TO$, Discount, NULL)) AS Discount FROM yourTable GROUP BY product;

In your example it is impossible to find the discount for a value such as 9.999... it is greater than 9.99 and less than 10 and does not fit inside any of your ranges. I would start by changing your data to:
 From  To  Discount    0  10  0.05   10  20  0.10   20  50  0.12   50  NULL  0.13 
And then you could probe the table like so:
SELECT Discount FROM Discounts WHERE (@Value >= [From]) AND (@Value < [To] OR [To] IS NULL)

Assuming you have no gaps in the ranges, I find that in many databases the most efficient method is:
select t.*, (select top 1 d.discount from discounts d where t.[$] <= d.[from$]  the $ is for whatever column name you actually use order by d.[from$] desc ) as discount from t;
(Note that this ignores
to$
.)In particular, this should be able to take advantage of an index on
discounts(from$, discount)
.