Tip: Table design and query for bracketed lookup

[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

 

[Allen Browne]

Hi Tom

Yes, Tom, I would be pleased to host this tip, with your name on it
(including the email address you posted from if you like.)

I have seen this question come up several times, and completely agree with
the approach you are suggesting. Will let you know when it is available.

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

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

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

 

[Allen Browne]

Okay, Tom, try this:
http://allenbrowne.com/ser-58.html

Please email me (address in the sig below) if there is anything you want
changed. I added a link for anyone who did not know what a subquery is, and
formatted it. Let me know if anything else needs adjusting.

All the best.

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

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

Hi Tom

Yes, Tom, I would be pleased to host this tip, with your name on it
(including the email address you posted from if you like.)

I have seen this question come up several times, and completely agree with
the approach you are suggesting. Will let you know when it is available.

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

 

[Marshall Barton]

Tom, Allen,

Be sure to include the other possible solution to this
situation. Actually, I think Allen was the one that
suggested it when we had this discussion several years ago.

SELECT TOP 1 Rate
FROM Bracket
WHERE Minimum <= Lookup
ORDER BY Minimum DESC

I know TOP is not a standard ANSI SQL predicate, but I
think most dialects include a near equivalent.
--
Marsh
MVP [MS Access]

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

 

[John Vinson]

Would one of you MVPs care to host this tip on a website where we could
reference it in our posts?

Copied to my "Access Tips" folder, and will be passed on to some folks
who maintain websites - good idea and excellent explanation! Thanks
Tom!

John W. Vinson[MVP]

 

[Tom Ellison]

Dear Allen:

You could go ahead with just what I posted here, if it is up to your
standards. If I refine it, I'll try to remember to let you (and others)
know!

It need be no secret this is part of my campaign to obtain re-nomination.
But, I also feel strongly about passing on some of what I've learned along
the way.

Tom Ellison

Hi Tom

Yes, Tom, I would be pleased to host this tip, with your name on it
(including the email address you posted from if you like.)

I have seen this question come up several times, and completely agree with
the approach you are suggesting. Will let you know when it is available.

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

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

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

 

[Tom Ellison]

Dear Marshall:

Busy, busy boy. Are you following me around?

I think I like this query better. It avoids nested subqueries and is
elegant. Too bad it's not ANSI, as you said. I should probably incorporate
it in my article, though. And, it works in Jet and MSDE, so it's completely
Access compatible.

Tom Ellison

Tom, Allen,

Be sure to include the other possible solution to this
situation. Actually, I think Allen was the one that
suggested it when we had this discussion several years ago.

SELECT TOP 1 Rate
FROM Bracket
WHERE Minimum <= Lookup
ORDER BY Minimum DESC

I know TOP is not a standard ANSI SQL predicate, but I
think most dialects include a near equivalent.
--
Marsh
MVP [MS Access]

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

 

[Tom Ellison]

Dear John:

I could improve this, including by some suggestions I have received here.
If an update comes along, I'll try to let you know.

A Word document makes a better source for posting to a web site, doesn't it?
I could include tables and other nice features. I've gone that way in the
past for articles. That works for you, doesn't it?

Tom Ellison

 

[Tom Ellison]

Dear Allen:

I've read it. I greatly approve and appreciate your enhancements.

When I wrote this, I wrote it as a response to a post in the NGs. Then I
thought it better to keep around as an article to which on can refer. It
still contains references to the "poster" and to"the person requesting
assistance." That is odd in the context of your "tips" section, and I would
like to iron that out as time permits. Hopefully yet this month. But I
believe the core of what I wanted to say is there.

Thanks again for your effort and affording me space on your site. You also
attribute articles on your menu of links to articles. Does my name belong
there? Maybe if I get famous first? : )

Tom Ellison

Okay, Tom, try this:
http://allenbrowne.com/ser-58.html

Please email me (address in the sig below) if there is anything you want
changed. I added a link for anyone who did not know what a subquery is,
and formatted it. Let me know if anything else needs adjusting.

All the best.

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

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

Hi Tom

Yes, Tom, I would be pleased to host this tip, with your name on it
(including the email address you posted from if you like.)

I have seen this question come up several times, and completely agree
with the approach you are suggesting. Will let you know when it is
available.

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

 

[Tom Ellison]

Dear Allen:

It's not JUST vanity to ask for my name there. I've launched my campaign to
get re-nominated MVP as well. I don't want MS to miss that!

Tom Ellison

Dear Allen:

I've read it. I greatly approve and appreciate your enhancements.

When I wrote this, I wrote it as a response to a post in the NGs. Then I
thought it better to keep around as an article to which on can refer. It
still contains references to the "poster" and to"the person requesting
assistance." That is odd in the context of your "tips" section, and I
would like to iron that out as time permits. Hopefully yet this month.
But I believe the core of what I wanted to say is there.

Thanks again for your effort and affording me space on your site. You
also attribute articles on your menu of links to articles. Does my name
belong there? Maybe if I get famous first? : )

Tom Ellison

Okay, Tom, try this:
http://allenbrowne.com/ser-58.html

Please email me (address in the sig below) if there is anything you want
changed. I added a link for anyone who did not know what a subquery is,
and formatted it. Let me know if anything else needs adjusting.

All the best.

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

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

Hi Tom

Yes, Tom, I would be pleased to host this tip, with your name on it
(including the email address you posted from if you like.)

I have seen this question come up several times, and completely agree
with the approach you are suggesting. Will let you know when it is
available.

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

 

[Allen Browne]

Yes, I should have put your name next to the article on the Tips index page.
Done.

No problem if you update the wording.

If you want to write another one, I have been considering an introductory
article on subqueries, but have not got round to it yet. Basic stuff for
users who don't understand SQL, explaining what a subquery is and a simple
example of why they might want to use one. Perhaps how to mock one up up a
query in the graphical query window, alias the table (Properties window),
switch to SQL view, and then paste the whole thing into the Field row of the
original query. Limitations (single return value issue, read only issue,
multi-level group-by issue with reports.)

No pressure at all, but you have a good grasp of SQL so if you can explain
this simply it would be a very useful topic, we could all refer to. The
trick would be to make it a suitable introduction for casual users.

 

[Tom Ellison]

Dear Allen:

Thanks for the kind consideration. Appreciated.

I really should write some. I expect there is some "credit" for this toward
restoring MVP status. I know they regard a web-site like yours as a plus.
And if any of you drop a hint that I've authored this, and any others,
inside the private NG, so it isn't missed, that could help as well I
suppose. I may as well campaign blatantly.

I already have a list of things I'd like to post, and some are more than
half finished writing. I'll keep your suggestion in mind. I certainly post
enough on subqueries here. I'd better know something about them!

Tom Ellison

 

[John Vinson]

Dear John:

I could improve this, including by some suggestions I have received here.
If an update comes along, I'll try to let you know.

A Word document makes a better source for posting to a web site, doesn't it?
I could include tables and other nice features. I've gone that way in the
past for articles. That works for you, doesn't it?

Not having published any articles (on the web or on paper) I can't
speak from experience, but I'd say that Word would lead to a more
readable and flexible document.

John W. Vinson[MVP]