I have a table of individuals and another table of transactions relating to those individuals. I want to find the most recent transaction for each individual in the database.
I have tried the following but this only gives me data for the most recent transaction in the database, not the most recent for each individual
Would be really grateful if someone could point me in the right direction. Thanks
I have tried the following but this only gives me data for the most recent transaction in the database, not the most recent for each individual
Code:
SELECT
tblIndividuals.individual_id,
tblIndividuals.forename,
tblIndividuals.surname,
tblTransactionsIndividuals.transaction_date,
tblTransactionsIndividuals.amount,
tblTransactionsIndividuals.transaction_id
FROM tblIndividuals
LEFT JOIN tblTransactionsIndividuals ON tblIndividuals.individual_id = tblTransactionsIndividuals.individual_id
WHERE tblTransactionsIndividuals.transaction_date =
(SELECT MAX(tblTransactionsIndividuals.transaction_date) FROM tblTransactionsIndividuals)
GROUP BY tblIndividuals.individual_id
ORDER BY tblIndividuals.renewal_date;
Would be really grateful if someone could point me in the right direction. Thanks