[AccessD] Selecting Top 10 for each group member

Do you need this at the query level? If the result is a report, you could
accomplish this at the report/subreport level instead.

Susan H.


> 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
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>