Top 5 Customers

[S Gianni]

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.

 

[Brian Camire]

It looks like it might be returning the bottom 5. For the top 5, try adding
DESC at the end of the ORDER clause, since you want to order the total sales
in descending order (i.e., from highest to lowest), as in:

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] 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.

-----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





.