Top X for all groups

S

Steve A

I'm trying to create a query on a SQL Server table in Access 2000 that at
it's basic level has 3 columns:

AccountNumber
DateCollected
UsageData

There are 1 or more records for each AccountNumber.

What I want to return are the 12 most recent records (or a lesser number if
12 do not exist), based on DateCollected for each AccountNumber.

When I try using Top 12 in the query builder, I end up with 1 record for
each AccountNumber, DateCollected, UsageData combination. Is there an easy
way to get the results I want in a query?
 
C

Chris Nebinger

What is your PK?

I have a banking ledger that has something along the same
lines:

ID
ExpenseType
Amount
TransactionDate


So, this query works:

SELECT bank.ExpenseType, bank.TransactionDate,
bank.Amount, bank.ID, bank.ID
FROM bank
WHERE bank.ID In (select Top 12 ID from bank AS bank1
Where bank1.ExpenseType=bank.expensetype ORDER BY Amount
DESC,transactiondate desc)
ORDER BY bank.ExpenseType, bank.Amount DESC , bank.ID;


Let me know if you need more help...

Chris Nebinger
 
M

Matt

Make sure that DateCollected is the first field in the
query window. then click the totals button and select
group by in the DateCollected field.
 
Top