Ranking

[Chris]

I need to design a process that will show a rep his/her top ten customers
based on sales increase from the past 60 days comapared to the same time
period from the prior year. I also need to show a rep his/her bottom ten
customers based on decrease from the past 60 days compared to the same time
period from the prior year. I know how to grab the data and do the
comparisons to come up with the increases and decreases for a reps entire
customer base but I don't know how to limit the out put to just show the top
ten based on increase or the bottom ten based on decrease. Any sugestions?

Thanks,
Chris

 

[Guest]

Have you tried using the TOP predicate in your query? to get those based on
the greatest increase, you would have to ORDER By on the increase Descending
and Ascending for the lowest. This assumes your query returns 1 row per
customer.

 

[Chris]

I've thought about the ascending and descending option, too but I need to
get the top 10 customers per rep. So every time there is a new rep number I
need to get their top ten customers. Any suggestions? Can I do this with
code somehow. If I were only doing this for one rep the
ascending/descending thing would be perfect. However, I'm dealing with
multiple reps.

 

[Guest]

Just off the top of my head ( the part with no hair), I would think you could
write a Sub that would open a table or query that would present a list of the
reps. The loop through that, and for each rep, run the query as an append
query and append the data to a temporary table.

 

[Neil Sunderland]

I need to design a process that will show a rep his/her top ten customers
based on sales increase from the past 60 days comapared to the same time
period from the prior year. I also need to show a rep his/her bottom ten
customers based on decrease from the past 60 days compared to the same time
period from the prior year. I know how to grab the data and do the
comparisons to come up with the increases and decreases for a reps entire
customer base but I don't know how to limit the out put to just show the top
ten based on increase or the bottom ten based on decrease. Any sugestions?

This is one way to do it.

First, create a query that returns all the reps and customers and the
%age change in sales (and since you said you knew how to do this, I
shan't bother <g>); I'll assume it's called qryTrends and it returns
something like this:

RepID CustID Trend
Fred Wilma 8.0
Arthur Estelle 7.6
Fred Betty 7.4
....
Arthur Bea -9.4
Arthur Susan -10.0
Fred Pebbles -10.8
Arthur Rue -11.5

Then, a ranking query (call it qryTrendUp):

SELECT Q.RepID, Q.CustID, Q.Trend,
(SELECT COUNT(*)
FROM qryTrends AS Q2
WHERE Q2.Trend >= Q.Trend
AND Q2.RepID = Q.RepID) AS Rank
FROM qryTrends AS Q

RepID CustID Trend Rank
Fred Wilma 8.0 1
Arthur Estelle 7.6 1
Fred Betty 7.4 2
....
Arthur Bea -9.4 60
Arthur Susan -10.0 61
Fred Pebbles -10.8 24
Arthur Rue -11.5 23

The top ten customers per rep is then just:
SELECT * FROM qryTrendUp WHERE Rank <= 10


To get the bottom 10, you just reverse the comparison in the first
ranking query (qryTrendDown):
SELECT Q.RepID, Q.CustID, Q.Trend,
(SELECT COUNT(*)
FROM qryTrends AS Q2
WHERE Q2.Trend <= Q.Trend
AND Q2.RepID = Q.RepID) AS Rank
FROM qryTrends AS Q

RepID CustID Trend Rank
Fred Pebbles -10.8 1
Arthur Rue -11.5 1
Arthur Susan -10.0 2
Arthur Bea -9.4 3
....
Fred Betty 7.4 23
Arthur Estelle 7.6 61
Fred Wilma 8.0 24

....and your worst ten is simply:
SELECT * FROM qryTrendDown WHERE Rank <= 10

 

[Chris]

Thanks! I will try this tomorrow.

This is one way to do it.

First, create a query that returns all the reps and customers and the
%age change in sales (and since you said you knew how to do this, I
shan't bother <g>); I'll assume it's called qryTrends and it returns
something like this:

RepID CustID Trend
Fred Wilma 8.0
Arthur Estelle 7.6
Fred Betty 7.4
...
Arthur Bea -9.4
Arthur Susan -10.0
Fred Pebbles -10.8
Arthur Rue -11.5

Then, a ranking query (call it qryTrendUp):

SELECT Q.RepID, Q.CustID, Q.Trend,
(SELECT COUNT(*)
FROM qryTrends AS Q2
WHERE Q2.Trend >= Q.Trend
AND Q2.RepID = Q.RepID) AS Rank
FROM qryTrends AS Q

RepID CustID Trend Rank
Fred Wilma 8.0 1
Arthur Estelle 7.6 1
Fred Betty 7.4 2
...
Arthur Bea -9.4 60
Arthur Susan -10.0 61
Fred Pebbles -10.8 24
Arthur Rue -11.5 23

The top ten customers per rep is then just:
SELECT * FROM qryTrendUp WHERE Rank <= 10


To get the bottom 10, you just reverse the comparison in the first
ranking query (qryTrendDown):
SELECT Q.RepID, Q.CustID, Q.Trend,
(SELECT COUNT(*)
FROM qryTrends AS Q2
WHERE Q2.Trend <= Q.Trend
AND Q2.RepID = Q.RepID) AS Rank
FROM qryTrends AS Q

RepID CustID Trend Rank
Fred Pebbles -10.8 1
Arthur Rue -11.5 1
Arthur Susan -10.0 2
Arthur Bea -9.4 3
...
Fred Betty 7.4 23
Arthur Estelle 7.6 61
Fred Wilma 8.0 24

...and your worst ten is simply:
SELECT * FROM qryTrendDown WHERE Rank <= 10

--
Neil Sunderland
Braunton, Devon

Please observe the Reply-To address