Top 5

  • Thread starter Thread starter S Gianni
  • Start date Start date
S

S Gianni

Hello,

I have a table which lists records by sales rep,
customers, and sales dollars along with other fields. I
am trying to pull out the records with the top five
customers in sales dollars for every sales rep. How do I
pull out the top 5 customers per sales rep? Any
assistance would be appreciated. Thank you.
 
This could be done with a subquery. Something like
SELECT tblA.*
FROM tblA
WHERE CustID IN (SELECT Top 5 CustID FROM tblA A WHERE A.SalesRep =
tblA.SalesRep ORDER BY SalesTotal DESC)
 
I tried this sub-query and something must be wrong because
it is not capturing the top 5 customers per customer rep.
Attached is what I typed


SELECT [Sales Rep Sales by Customer].*
FROM [Sales Rep Sales by Customer]
WHERE ((([Sales Rep Sales by Customer].[Cust #]) In
(SELECT Top 5 [Cust #] FROM [Sales Rep Sales by Customer]
A WHERE A.[Sales Rep] = [Sales Rep Sales by Customer].
[Sales Rep] ORDER BY [Sales Rep Sales by Customer].
[SumOfTotal Sales])));

Does anybody see an error with this SQL statement?

Thank you.
 
Try adding in the "DESC" as suggested previously.
ORDER BY [Sales Rep Sales by Customer].
[SumOfTotal Sales] DESC)));
Click to expand...
--
Duane Hookom
MS Access MVP


S Gianni said:
I tried this sub-query and something must be wrong because
it is not capturing the top 5 customers per customer rep.
Attached is what I typed


SELECT [Sales Rep Sales by Customer].*
FROM [Sales Rep Sales by Customer]
WHERE ((([Sales Rep Sales by Customer].[Cust #]) In
(SELECT Top 5 [Cust #] FROM [Sales Rep Sales by Customer]
A WHERE A.[Sales Rep] = [Sales Rep Sales by Customer].
[Sales Rep] ORDER BY [Sales Rep Sales by Customer].
[SumOfTotal Sales])));

Does anybody see an error with this SQL statement?

Thank you.
-----Original Message-----
This could be done with a subquery. Something like
SELECT tblA.*
FROM tblA
WHERE CustID IN (SELECT Top 5 CustID FROM tblA A WHERE A.SalesRep =
tblA.SalesRep ORDER BY SalesTotal DESC)

--
Duane Hookom
MS Access MVP





.
Click to expand...
Click to expand...
 
I reconstructed your query using two aliases in order to make it easier for me
to read. I found that you weren't specifically identifying fields in the
subquery and your were trying to do an order by in the subquery by a field in
the primary query. Also, you were sorting in the wrong order.

SELECT S.*
FROM [Sales Rep Sales by Customer] AS S
WHERE S.[Cust #] In
(SELECT Top 5 A.[Cust #]
FROM [Sales Rep Sales by Customer] as A
WHERE A.[Sales Rep] = S.[Sales Rep]
ORDER BY A.[SumOfTotal Sales] Desc)



S said:
I tried this sub-query and something must be wrong because
it is not capturing the top 5 customers per customer rep.
Attached is what I typed

SELECT [Sales Rep Sales by Customer].*
FROM [Sales Rep Sales by Customer]
WHERE ((([Sales Rep Sales by Customer].[Cust #]) In
(SELECT Top 5 [Cust #] FROM [Sales Rep Sales by Customer]
A WHERE A.[Sales Rep] = [Sales Rep Sales by Customer].
[Sales Rep] ORDER BY [Sales Rep Sales by Customer].
[SumOfTotal Sales])));

Does anybody see an error with this SQL statement?

Thank you.
-----Original Message-----
This could be done with a subquery. Something like
SELECT tblA.*
FROM tblA
WHERE CustID IN (SELECT Top 5 CustID FROM tblA A WHERE A.SalesRep =
tblA.SalesRep ORDER BY SalesTotal DESC)

--
Duane Hookom
MS Access MVP





.
Click to expand...
Click to expand...
 
Back
Top