Simple Query for a Range (Not date related)

[Tess2819]

Hi Everyone,

This is my first topic so I hope you can help. I have searched but
can't seem to find what I am looking for, so here it is.

I want to create a query using design view in MS-Access that will show
the award amount for each persons sales each month based on the range
shown between High and Low Points.

Any help would be greatly appreciated! Thanks

Here are the columes I am dealing with

Sales Table :

ID Code Participant August September October
1 12526 Joe Smith 471 387 525
2 12556 Mary Lou 363 425 570
3 12586 Dan Leblanc 391 357 539
4 12544 Marc Breau 448 375 459
5 12698 Lucy White 354 400 490
Range Table

ID Low Points High Points Award
2 600 2000 $250.00
3 550 599 $225.00
4 500 549 $200.00
5 450 499 $175.00
6 400 449 $150.00
7 350 399 $125.00
8 300 349 $100.00
9 250 299 $75.00
10 200 249 $50.00
11 1 199 $25.00

[CDMAPoster]

Tess2819 wrote:

Hi Everyone,

This is my first topic so I hope you can help. I have searched but
can't seem to find what I am looking for, so here it is.

I want to create a query using design view in MS-Access that will show
the award amount for each persons sales each month based on the range
shown between High and Low Points.

Any help would be greatly appreciated! Thanks

Here are the columes I am dealing with

Sales Table :

ID Code Participant August September October
1 12526 Joe Smith 471 387 525
2 12556 Mary Lou 363 425 570
3 12586 Dan Leblanc 391 357 539
4 12544 Marc Breau 448 375 459
5 12698 Lucy White 354 400 490
Range Table

ID Low Points High Points Award
2 600 2000 $250.00
3 550 599 $225.00
4 500 549 $200.00
5 450 499 $175.00
6 400 449 $150.00
7 350 399 $125.00
8 300 349 $100.00
9 250 299 $75.00
10 200 249 $50.00
11 1 199 $25.00

Using the data from your example:

qryAugustAward:
SELECT ID, Participant, [August], (SELECT Max(A.Award) FROM [Range] AS
A WHERE A.[Low Points] <= Sales.[August]) AS AugustAward FROM Sales;

!qryAugustAward:
ID Participant August AugustAward
1 Joe Smith 471 $175.00
2 Mary Lou 363 $125.00
3 Dan Leblanc 391 $125.00
4 Marc Breau 448 $150.00
5 Lucy White 354 $125.00

then insert:

, [September], (SELECT Max(A.Award) FROM [Range] AS A WHERE A.[Low
Points] <= Sales.[September]) AS SeptemberAward

and

, [October], (SELECT Max(A.Award) FROM [Range] AS A WHERE A.[Low
Points] <= Sales.[October]) AS OctoberAward

before ' FROM Sales' and change the name of the query :-). You really
don't want to hard code the SQL for each month like I've shown.
Perhaps another table can be used to narrow which months are selected
(with the year included) in the original crosstab used to obtain the
Sales table so that you know in advance which months will show up. My
intuition tells me that there's an elegant solution to this problem.

James A. Fortune
CD********@FortuneJames.com

[Jamey Shuemaker]

Looks like you have some normalization issues with this setup. You
shouldn't be keeping a field in Sales Table for every month of the
year. You would be better off with a table setup like this:

EmployeeTable:
EmpID (pk), Code, Name

SaleTable
EmpID, Year, Month, SaleTotal

The SaleTable should use a composite key of EmpID-Year-Month to
uniquely identify seperate records.

Your Range Table is fine except that the HighPoints value is superflous
and not necessary under this structure.

For the structure you currently have, this query will return the
appropriate bonus based on August sales data:

SELECT SaleTable.Participant, SaleTable.August,
DLookUp("Award","RangeTable","LowPoints<=" & [August]) AS Bonus
FROM SaleTable LEFT JOIN RangeTable ON SaleTable.August =
RangeTable.LowPoints;

Again, while this does the job, you should closely consider changing
your current data structure.

[CDMAPoster]

Jamey Shuemaker wrote:

Looks like you have some normalization issues with this setup. You
shouldn't be keeping a field in Sales Table for every month of the
year. You would be better off with a table setup like this:

EmployeeTable:
EmpID (pk), Code, Name

SaleTable
EmpID, Year, Month, SaleTotal

The SaleTable should use a composite key of EmpID-Year-Month to
uniquely identify seperate records.

Your Range Table is fine except that the HighPoints value is superflous
and not necessary under this structure.

For the structure you currently have, this query will return the
appropriate bonus based on August sales data:

SELECT SaleTable.Participant, SaleTable.August,
DLookUp("Award","RangeTable","LowPoints<=" & [August]) AS Bonus
FROM SaleTable LEFT JOIN RangeTable ON SaleTable.August =
RangeTable.LowPoints;

Again, while this does the job, you should closely consider changing
your current data structure.

I see something elegant and something risky in your solution.

>From A97 Help (DLookup Function):

If more than one field meets criteria, the DLookup function returns the
first occurrence.
The elegant part is that it takes advantage of this behavior.

The risky part is that taking advantage of this behavior makes a risky
assumption.

In deciding the first occurrence, you're aided by the fact that ID
values exist. When ID values exist, Access usually uses them as a
default ordering for the table. Because DLookup does not specify how
the ordering is done, you're relying on that default ordering behavior.

Also from A97 Help (DLookup Function):

Whether you use the DLookup function in a macro or module, a query
expression, or a calculated control, you must construct the criteria
argument carefully to ensure that it will be evaluated correctly.
Maybe DMax would be safer?

Also, perhaps the OP can tell us whether the Sales Table is created by
a crosstab query or is created directly. If it is created directly I
agree with your normalization comments.

James A. Fortune
CD********@FortuneJames.com

[Jamey Shuemaker]

CD********@FortuneJames.com wrote:

Jamey Shuemaker wrote:

I see something elegant and something risky in your solution.

From A97 Help (DLookup Function):

If more than one field meets criteria, the DLookup function returns the
first occurrence.
The elegant part is that it takes advantage of this behavior.

The risky part is that taking advantage of this behavior makes a risky
assumption.

Very true. I meant to comment on the reliance upon order, above, but
simply forgot. This will be particularly problematic if the balance
structure changes....say, we add a range like:

ID Low Award
12 2001 $300

Seems the balance structure has already changed once, since our ID=1
value has already dropped out of the table.

Probably better, in the long run, to normalize the tables, as above,
then setup a more rigorous query, like you mentioned in your first
response (obviously, altered for the new, normalized, table structure).