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) :
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!
Can anybody point me in a direction?
Thanks
Al
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
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)
Thanks
Al