T
Tom Ellison
Dear NG:
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 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 .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 restriciting the comparision 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.
Would one of you MVPs care to host this tip on a website where we could
reference it in our posts?
Tom Ellison
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 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 .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 restriciting the comparision 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.
Would one of you MVPs care to host this tip on a website where we could
reference it in our posts?
Tom Ellison