Query:

[Sachin Nandula]

HEy All...

Funny predicament I'm in. Look at the the two sample tables below.

MASTER TABLE:
Acct# Revenue by Month
-----------------------
123 $100
123 $200
123 $50
123 $10
456 $200
456 $300

SLAVE TABLE:
Acct# Top 25 Rev by Month
-------------------------
123 $360
456 $500

The Master Table lists Account Numbers and Revenue by Month. I took
the month field out. So the REV field can have like 500 different
revenue numbers associated with it.
How can I take only the TOP 25 Revenue numbers by each account so that
it looks like the SLAVE TABLE?
I.E. If I do this by hand, I would first have display each account's
Revenue numbers, sort the Revenue numbers Descending, and take the TOP
25 revenue numbers for that ONE account and sum the Revenue. I would
have to do this for about 700 accounts.

Can someone lend me a hand please. I think my criteria field needs to
have a SQL statement in there, butt he ones I try are not producing
the results I need.

Thanks so much in advance.

 

[John Spencer (MVP)]

SELECT [Acct#], [RevenueByMonth]
FROM [MasterTable] as M
WHERE [RevenueByMonth]
IN (Select Top 25 T.[RevenueByMonth]
FROM [MasterTable] as T
WHERE T.[Acct#] = M.[Acct#]
ORDER BY T.[RevenueByMonth] Desc)