Rankings baseline performance

[zartan]

I am trying to run a query that will rank employees sales based on a
series of baselines I have created. Let's say I have a table called
t-salesbaselineN which looks like this:

SalesAMT Level
1501-2000 1
1000-1500 2
0-999 3

And a table called t-salesbaselineS like this:

SalesAMT Level
2501-4000 1
2000-2500 2
0-2000 3


Then I have a Sales table called t-empsales which looks like this:

Name Sales Region
Bob 1001 N
Jim 1501 N
Joe 800 S
Ted 1001 S

I need to run a query which will produce a result that looks like this.
Which basically ranks based on my baselines and the region the employee
is in.

Name Sales Region Rank
Bob 1001 N 1
Jim 1501 N 2
Joe 800 S 3
Ted 1001 S 3

Can something like this be done? I've spent the last couple weeks
scouring the previous posts and I can't find anything exactly what I
am looking for. Any help I can get would be greatly appreciated

Thanks

Jeremy

 

[tina]

putting region data in table names (N and S) breaks normalization rules, as
does using one field to hold two values (minimum and maximum). you should
have one table of baseline data, as

tblSalesBaseline
baseID (primary key)
Region
SalesMinimum
SalesMaximum
Level

your data will look like

Region SalesMinimum SalesMaximum Level
S 2501 4000 1
S 2001 2500 2
S 0 2000 3
N 1501 2000 1
N 1000 1500 2
N 0 999 3

now that the data is normalized in one table, you can use a DLookUp() in the
query to pull the Level for each record. add a calculated field to the
query, as

Rank: DLookUp("[Level]", "tblSalesBaseline", "[Region] = '" & [Region] & "'
And [SalesMinimum] <= " & [Sales] & " And [SalesMaximum] >= " & [Sales])

the above goes all on one line, on the Field: line. if you have a lot of
records, the query may run slowly due to performing a DLookUp() on each
record, but that's the simplest way i can come up with at the moment.
perhaps somebody who's better at SQL will post a solution that runs faster.

hth