Top X for all groups

  • Thread starter Thread starter Steve A
  • Start date Start date
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?
 
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
 
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.
 
Back
Top