Ranking with Multiple Variables

G

Guest

The query this is based off has many fields but the most important are:
Rep, Office Location, TotalAlert, and Conversion%

I need to create a query that will give me the top 10 reps (those with the
lowest Conversion% in ascending order with the TotalAlert in descending
order) for each Office Location.

For example:

Rep OfficeLoc TotalAlert Conversion%
Mike Hawaii 100 0.0%
Rachel Hawaii 99 0.0%
Sam Hawaii 101 1.0%
Dan Hawaii 80 2.0%

I have tried the following sql (which I found here somewhere), but the
ranking is wrong & all over the place. And, if I use Top 10, then I don't
receive the Top 10 for each location - which is what I need.

Help!

SELECT qryYTDRepSkidAlertStats.OGIDNO, qryYTDRepSkidAlertStats.SignON,
qryYTDRepSkidAlertStats.Rep, qryYTDRepSkidAlertStats.Accept,
qryYTDRepSkidAlertStats.Decline, qryYTDRepSkidAlertStats.TotalAlert,
qryYTDRepSkidAlertStats.[Conversion%], qryYTDRepSkidAlertStats.Phone,
qryYTDRepSkidAlertStats.Phneweb, qryYTDRepSkidAlertStats.TotalPhoneOrders,
qryYTDRepSkidAlertStats.[Opportunity%], qryYTDRepSkidAlertStats.[Office
Location],

(Select Count(*) from qryYTDRepSkidAlertStats as T1 where T1.[Office
Location]=qryYTDRepSkidAlertStats.[Office Location] And
T1.[Conversion%]<=qryYTDRepSkidAlertStats.[Conversion%] AND
T1.[TotalAlert]>=qryYTDRepSkidAlertStats.[TotalAlert]) AS Rank

FROM qryYTDRepSkidAlertStats

WHERE (Select Count(*) from qryYTDRepSkidAlertStats as T1 where T1.[Office
Location]=qryYTDRepSkidAlertStats.[Office Location] And
T1.[Conversion%]<=qryYTDRepSkidAlertStats.[Conversion%] AND
T1.[TotalAlert]>=qryYTDRepSkidAlertStats.[TotalAlert]) <= 10

ORDER BY qryYTDRepSkidAlertStats.[Office Location],
qryYTDRepSkidAlertStats.[Conversion%], qryYTDRepSkidAlertStats.TotalAlert
DESC;

This is what it returns:

TotalAlert Conversion% Rank
144 0.00% 1
121 0.00% 2
81 0.00% 3
79 0.00% 4
62 0.00% 5
56 0.00% 6
36 0.00% 7
17 0.00% 8
92 2.17% 3
44 2.27% 9
44 2.27% 9
168 2.38% 1
 
J

John Spencer

One way to handle this is to use a correlated subquery

SELECT OfficeLoc, Rep, TotalAlert, [Conversion%]
FROM qryYTDRepSkidAlertStats as Q1
WHERE Q1. [Conversion%] in
(SELECT Top 10% [Conversion%]
FROMqryYTDRepSkidAlertStats as Q2
WHERE Q2.OfficeLoc = Q1.OfficeLoc
ORDER BY Q2. [Conversion%])
ORDER BY OfficeLoc, [Conversion%], TotalAlert Desc

That will return ties for the last position if ties exist.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Jen said:
The query this is based off has many fields but the most important are:
Rep, Office Location, TotalAlert, and Conversion%

I need to create a query that will give me the top 10 reps (those with the
lowest Conversion% in ascending order with the TotalAlert in descending
order) for each Office Location.

For example:

Rep OfficeLoc TotalAlert Conversion%
Mike Hawaii 100 0.0%
Rachel Hawaii 99 0.0%
Sam Hawaii 101 1.0%
Dan Hawaii 80 2.0%

I have tried the following sql (which I found here somewhere), but the
ranking is wrong & all over the place. And, if I use Top 10, then I don't
receive the Top 10 for each location - which is what I need.

Help!

SELECT qryYTDRepSkidAlertStats.OGIDNO, qryYTDRepSkidAlertStats.SignON,
qryYTDRepSkidAlertStats.Rep, qryYTDRepSkidAlertStats.Accept,
qryYTDRepSkidAlertStats.Decline, qryYTDRepSkidAlertStats.TotalAlert,
qryYTDRepSkidAlertStats.[Conversion%], qryYTDRepSkidAlertStats.Phone,
qryYTDRepSkidAlertStats.Phneweb, qryYTDRepSkidAlertStats.TotalPhoneOrders,
qryYTDRepSkidAlertStats.[Opportunity%], qryYTDRepSkidAlertStats.[Office
Location],

(Select Count(*) from qryYTDRepSkidAlertStats as T1 where T1.[Office
Location]=qryYTDRepSkidAlertStats.[Office Location] And
T1.[Conversion%]<=qryYTDRepSkidAlertStats.[Conversion%] AND
T1.[TotalAlert]>=qryYTDRepSkidAlertStats.[TotalAlert]) AS Rank

FROM qryYTDRepSkidAlertStats

WHERE (Select Count(*) from qryYTDRepSkidAlertStats as T1 where T1.[Office
Location]=qryYTDRepSkidAlertStats.[Office Location] And
T1.[Conversion%]<=qryYTDRepSkidAlertStats.[Conversion%] AND
T1.[TotalAlert]>=qryYTDRepSkidAlertStats.[TotalAlert]) <= 10

ORDER BY qryYTDRepSkidAlertStats.[Office Location],
qryYTDRepSkidAlertStats.[Conversion%], qryYTDRepSkidAlertStats.TotalAlert
DESC;

This is what it returns:

TotalAlert Conversion% Rank
144 0.00% 1
121 0.00% 2
81 0.00% 3
79 0.00% 4
62 0.00% 5
56 0.00% 6
36 0.00% 7
17 0.00% 8
92 2.17% 3
44 2.27% 9
44 2.27% 9
168 2.38% 1
Click to expand...
 
Top