[AccessD] Selecting Top 10 for each group member

Lawrence Mrazek lmrazek at lcm-res.com
Thu Sep 11 21:02:56 CDT 2003


Greetings:

I have a sales database that we need to report against. We need to find the
top 10 accounts for each sales territory and then view a report of the top
10 accounts for each territory showing order details. and I'm having trouble
figuring out how to do this with a query (if it is possible).

I can find the top ten accounts using code like this below, but I then have
to run through each territory, appending the results to an intermediate
table, which I can then join to the master table to get the Top 10 Accounts
for each territory (joining SalesRepCode, BillToNumber). Of course, this is
quite clumsy; I know there must be some other way of doing this. 

SELECT TOP 10 table1.SalesRepCode, table1.BillToNumber,
Sum(table1.MarginYTDThisYear) AS MarginYTDThisYear
FROM table1
GROUP BY table1.SalesRepCode, table1.BillToNumber
HAVING (((table1.SalesRepCode)="code"))
ORDER BY table1.SalesRepCode, Sum(table1.MarginYTDThisYear) DESC;

The query above produces something like this:
SalesRepCode	BillToNumber	MarginYTDThisYear
23			1			$99999.99
23			2			$88888.88
23			3			$77777.77
23			3			$66666.66
23			5			$55555.55
23			6			$44444.44
23			7			$33333.33
23			8			$22222.22
23			9			$11111.11
23			10			$01111.11


Any hints/pointers would be very helpful. Thanks in advance for any help.

Larry Mrazek
LCM Research, Inc. 
ph. 314-997-2574
fx. 314-997-2620
lmrazek at lcm-res.com
http://www.lcm-res.com



More information about the AccessD mailing list