Top 5 transactions per Customer

[Pat]

Is there a way via queries to get the top 5 transactions (latest dates) for many customers.
Or is it best to do it via VBA to loop thru all customers and get the top 5 transactions for each and store them in a table?

[HansV]

You can use a subquery. Let's say that the table is named tblTransactions and that the relevant fields are CustomerID and TransactionDate.

Code: Select all

SELECT * FROM tblTransactions 
WHERE CustomerID In 
  (SELECT Top 5 T.CustomerID 
   FROM tblTransactions AS T 
   WHERE T.CustomerID=tblTransactions.CustomerID 
   ORDER BY T.TransactionDate DESC)
ORDER BY CustomerID, TransactionDate DESC

[Pat]

Neat Hans, well done!!

Oops spoke too soon.

Is CustomerID in fact a customer number where the PK is CustomerID/TransactionDate?

It returns all records, not the top 5 for each CustomerID.

[JohnH]

If I add a TransactionID field to tblTransactions (as the key) this works.

Code: Select all

SELECT *
FROM tblTransactions
WHERE (((TransactionID) In (SELECT Top 5 T.TransactionID 
   FROM tblTransactions AS T 
   WHERE T.CustomerID=tblTransactions.CustomerID 
   ORDER BY T.TransactionDate DESC)))
ORDER BY tblTransactions.CustomerID, tblTransactions.TransactionDate DESC;

[Pat]

Thanks John, works well