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?
Top 5 transactions per Customer
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Top 5 transactions per Customer
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
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Top 5 transactions per Customer
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.
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.
You do not have the required permissions to view the files attached to this post.
-
- 3StarLounger
- Posts: 287
- Joined: 09 Mar 2010, 23:16
- Location: Canberra Australia
Re: Top 5 transactions per Customer
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;
Regards
John
John
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: Top 5 transactions per Customer
Thanks John, works well