Lookup a value in a range

        0 votes: *****     3,185 views      No comments
by Allen Browne, 20 May 2009    (for Access 95+)

Microsoft Access Tips for Serious Users

Provided by Tom Ellison, February 2006.  Updated March 2007.


Lookup a value in a range

There is a common approach to bracketed tables and lookups. It goes something like this:

BracketLow BracketHigh Rate
0.00 9.99 0.05
10.00 19.99 0.10
20.00 49.99 0.12
50.00 99999999.00 0.13

With this, use a query:

SELECT Rate
  FROM Bracket
  WHERE [Enter Bracket:]
    BETWEEN BracketLow and BracketHigh

I would prefer not to use this solution.

As soon as you give users the ability to make mistakes, you have created problems. If users are allowed to create brackets with both their beginning and ending points, they will almost certainly create brackets that overlap or have gaps. The above table actually has gaps, which will become apparent if the value sought is 9.993. No rows would be returned by the query!

Instead, putting only one endpoint in each row of the Rate table is sufficient. While the query work is indeed not as simple to write, it will perform well enough, as the number of rows in the Rate table would almost certainly be few. Indeed, the index for the table would only be on this single value anyway, so that's the way Access will find the row(s) necessary.

There is a principle in database construction not to store derivable values. This principle could be interpreted to extend to this subject. You can derive the missing value, either upper or lower, of any bracket, as it is the value in either the previous or subsequent row's value for lower or upper (respectively) when ordered by that column.

The principle of not storing derivable values has exactly the same purpose in this case as in simpler cases, where the derivation is just between columns of the current row. That principle is that, when the derivable value is stored but not equal to what would be derived, then the stored value is incorrect, and the query will malfunction on that basis. The alternative is to check the derived value against the stored value and replace it where necessary. However, this entails a query at least as complex as the one you seek to avoid in just deriving the "missing" value when needed.

The query I propose generally requires a subquery to find the proper bracket, and this is slightly daunting to many who seek our advice here.

I expect that, by airing my point of view here, this will stimulate those we seek to assist to consider these alternatives. So, I will illustrate my approach for their consideration.

At a point in the query you build, you require a Rate for further calculation, or just to display, or both. This rate comes from a table of brackets something like this:

From To Rate
0.00 9.99 5%
10.00 19.99 10%
20.00 49.99 12%
50.00   13%

This last bracket represents "anything 50 or above."

There are two ways to store this: with the values in the From column, or with the values from the To column. In this case, I would choose the "even, whole values" to be stored, that is, the From column. The table would look like:

Minimum Rate
0.00 0.05
10.00 .10
20.00 .12
50.00 .13

In this table, I propose the Primary Key is the From value.

If the "lookup" value is in a column of your query called Lookup, then the subquery returning rate would be:

(SELECT Rate
  FROM RateTable RT1
  WHERE From =
    (SELECT MAX(Minimum)
      FROM  RateTable RT2
      WHERE Minimum <= Lookup))

Now this is a two tier subquery (yep, it's complex, and just the thing from which you probably wanted to shield the poster). Indeed, this is a problem, because Access Jet doesn't seem to handle this well much of the time. I believe that's because the Lookup in the inner query is two nesting levels away from its source in the outer query.

So now the solution becomes (sadly) even more complex. Actually, for the person requesting assistance, this may be better, however, as they can see what is happening step-by-step.

The solution is to build a query that has nearly the appearance of the original table with both From and To columns, deriving the To column. However, I will provide a To column that is .01 large than my illustration. The query using Lookup will have to find the bracket where Lookup >= From AND Lookup < To (NOT less than or equal!!!)

SELECT Minimum,
  (SELECT MIN(RT1.Minimum)
    FROM RateTable RT1
    WHERE RT1.Minimum > RT.Minimum)
  AS Maximum,
  Rate
  FROM RateTable RT

If you wish, you could reproduce exactly the original values by subtracting 0.01 from this Maximum. I prefer not to do this. If the query must calculate the value of Lookup, and the value is not rounded off to the nearest "penny" then it is possible that Lookup would be 9.993. In the original Rate Table, there is no value of Rate for 9.993. I know that we humans would probably choose the rate for the bracket for 0.00 to 9.99, but the computer will not do so. By deriving an upper limit as I have shown, and then restricting the comparison to be less than that value, this can be overcome, eliminating any "gaps" in the bracket structure. This is where a judicious choice of the column on which to base the actual data (the single endpoint approach) is useful, and that's why I chose the "whole values" column for this basis.

There is really no substitute for remembering to round the value when Lookup is calculated in order to make this work correctly. If you want 9.993 to be in the 0.00 to 9.99 bracket yet 9.996 to be in the 10.00 to 19.99 bracket, then you must round before using Lookup.


Footnotes:

[1] If subqueries are new, see: How to Create and Use Subqueries.


Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Lookup a value in a range'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments


Comments require approval before being displayed on this page (allow 24 hours).