Query last comment for each account

[xyle]

I need to make a query to get the last comment for each account. Each record has a timestamp I can use to find the last comment. The table is called tblActions, primary key is Action Id, the fields I need are AccountNo, DateOfAction, and Comments. The query I tried is

SELECT tblActions.AccountNo, Max(tblActions.DateOfAction) AS MaxOfDateOfAction, tblActions.Comments
FROM tblActions
GROUP BY tblActions.AccountNo, tblActions.Comments;

It gives me every record because of the Group By tblActions.Comments but I don’t know how to get around that and the primary key is comprised of a few different things so it is not exactly sequential.

 

[PHV]

What about this ?

SELECT A.AccountNo, A.DateOfAction, A.Comments
FROM tblActions A INNER JOIN (
SELECT AccountNo, Max(DateOfAction) AS MaxOfDateOfAction FROM tblActions GROUP BY AccountNo
) L ON A.AccountNo=L.AccountNo AND A.DateOfAction=L.MaxOfDateOfAction

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

 

[xyle]

Thanks I would never have gotten that