SELECT TOP 10 by group; multiple fields (AC2007) (1 Viewer)

AOB

Registered User.
Local time
Today, 14:21
Joined
Sep 26, 2012
Messages
613
Hi guys,

I have a query which retrieves a summation of fees grouped by two fields (business and client) :

Code:
SELECT IIf(qF.Business Is Null,"Unknown",qF.Business) AS Business, qF.Client AS Client, Sum(qF.Fee) AS SumOfFees
FROM qryFees AS qF
GROUP BY IIf(qF.Business Is Null,"Unknown",qF.Business), qF.Client
I now need to create another query which returns the top 10 clients (based on SumOfFees, descending) for each business

This is my effort but it's painfully slow and, worse, doesn't return 10 records per business!

Code:
SELECT qFBC.Business, qFBC.Client, qFBC.SumOfFees
FROM qryFeesByBusinessAndClient qFBC
WHERE EXISTS
    (SELECT TOP 10 qFBC1.Client, qFBC1.SumOfFees
     FROM qryFeesByBusinessAndClient qFBC1
     WHERE qFBC1.Business = qFBC.Business
     ORDER BY qFBC1.SumOfFees DESC)
Can anybody point me in a direction?

Thanks

Al
 

AOB

Registered User.
Local time
Today, 14:21
Joined
Sep 26, 2012
Messages
613
Took some adaptation but that works perfectly!

Thanks very very much jdraw!
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:21
Joined
Jan 23, 2006
Messages
15,364
Good stuff.
Happy to help.
 

Users who are viewing this thread

Top Bottom