Top X customers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a query (qryCustSales) with the following fields; customer, salesman,
and SumOfPrice. How do I get the top 15 customers for EACH salesman based on
the SumOfPrice?

Thanks in advance!
 
Try this

SELECT M1.customer, M1.salesman, M1.SumOfPrice
FROM qryCustSales AS M1
WHERE M1.customer In (SELECT Top 15 M2.customer
FROM qryCustSales as M2
WHERE M2.salesman =M1.salesman
ORDER BY M2.SumOfPrice Desc)
 
Dear Franky:

You can rank the customers by salesman, giving each customer a value
starting with 0 for the best customer and counting upward.

SELECT customer, salesman, SumOfPrice,
(SELECT COUNT(*) FROM qryCustSales Q2
WHERE Q2.salesman = Q1.salesman
AND Q2.SumOfPrice > Q1.SumOfPrice) AS Rank
FROM qryCustSales Q1

Save the above query as, say, MyQuery, then:

SELECT * FROM MyQuery WHERE Rank < 15

This then returns the customers with Ranks from 0 to 14.

Note that, if there is a 2 way tie for 15th place (Rank = 14) then BOTH
customers will appear. That seems very natural to me. We could add a
"tie-breaker" (such as using the customer's name as a secondary ranking
value) to arbitrarily eliminate one of the customers so you never have more
than 15.

Please let me know how this worked for you.

Tom Ellison
 
Back
Top