Trouble getting latest comment record from a query... 3

[ToyFox]

I have a table that holds comments. There can be multiple comments for the account. They are dated. I want to query the table and get the latest comment -- I am using MAX on the date and LATEST on the comment....I have checked several records and I am getting the MAX date but sometimes not the LATEST comment....is there a way to accomplish this.

 

[PHV]

A starting point:

SELECT A.account, A.date, A.comment
FROM yourTable A INNER JOIN (
SELECT account, MAX([date] AS LastDate FROM yourTable GROUP BY account
) M ON A.account=M.account AND A.Date=M.LastDate

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

 

[randy700]

Perhaps?

SELECT Comment FROM TableName WHERE CommentDate IN (SELECT Max(CommentDate) FROM TableName)

Randy

 

[ToyFox]

I got it to work by doing MAX on the date and first on the comment. I think when it runs, it does the Max date first which places that record first in the result set, then the comment is first. Does that make sense. I will use the sub query method instead....looks cleaner. Thanks

 

[lespaul]

So there can be multiple comments on the same day too?

Leslie

 

[ToyFox]

No, there wont be multiples on the same day.

 

[dhookom]

ToyFox and randy700,
Your solutions are not reliable. PH's is reliable. randy700's doesn't consider the account. I would never consider using "Last" in SQL since records in a table are not sorted.

Duane
Hook'D on Access
MS Access MVP