[AccessD] Selecting Top 10 for each group member

Hi Susan:

I'll (at least I think) need to be able to do both, since they need a
printed version and something that we can export to Excel. 

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

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Thursday, September 11, 2003 8:13 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
>

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com