Lawrence Mrazek
lmrazek at lcm-res.com
Fri Sep 12 11:11:17 CDT 2003
Thanks Gustav: I've decided to use my approach, since some of the sales territories share billto accounts (don't ask, data is coming off a mainframe system) it made some of my attempts at using subqueries produce strange results. Thanks for your help! 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 Gustav Brock Sent: Friday, September 12, 2003 8:07 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Selecting Top 10 for each group member Hi Lawrence I don't think your method is that clumsy, actually I guess it is pretty fast compared to joining the top10 query and the specifications query. Alternatively, you could state IN (Select AccountID From <name-of-top10-sum-query>) as the where criteria for AccountID in your specifications query. /gustav > 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 > _______________________________________________ > 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