Returning latest transaction

[moonbase]

I am sure there's a simple way to do this but I can't get my head round it.
I have a master table with accounts (account no, name, etc) and a linked table with transactions, many per account, (date, amount, etc).
Is there an SQL statement to retrive the name from the master table with the date and amount from the most recent by date transaction?
I can use LAST but the last transaction may not have the most recent date.
I tried MAX on the date but don't know how to get the amount on that transaction.
Thanks

 

[Gammachaser]

Assuming the tables are tied by AccountNo, try:

SELECT * FROM tblAccounts WHERE AccountNo IN(SELECT AccountNo FROM tblTransactions WHERE TransactionDate IN(SELECT Max(TransactionDate) FROM tblTransactions))

This method will give you the the information for the account number with the newest transaction date. If there is a 'tie' (i.e. two transactions with the same value in the date field) you will get both account numbers and therefore both sets of information. So, if the data in the date field includes the time, then you should get only the transactions that occurred within the same second; if not, then you get all the accounts that had a transaction on that day.

Naturally, you need to substitute your table and field names, since you did not include them in the original post.

 

[moonbase]

Many thanks. This has pointed me in the right direction and I'll play around with it later.

 

[PHV]

A starting point:

SELECT A.*, T.*
FROM (tblAccounts A
INNER JOIN tblTransactions T ON A.[account no] = T.[account no])
INNER JOIN (
SELECT [account no], Max([date]) AS LastDate FROM tblTransactions GROUP BY [account no]
) L ON T.[account no] = L.[account no] AND T.date = L.LastDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886