Help with returning the newest record in a query

[JessM]

I have a form that comments can be entered into and receive a time stamp. So
I end up with multiple comments per project number. I want to run a query
that only returns the newest comment. Below is what I tried, but it is only
returning the most new comment and not the newest comment by project number.

SELECT Data_ProjectComments.Project_Number,
Data_ProjectComments.CurrentTime, Data_ProjectComments.CommentCode,
Data_ProjectComments.Comment
FROM Data_ProjectComments
WHERE (((Data_ProjectComments.CurrentTime)=(SELECT MAX(CurrentTime) FROM
Data_ProjectComments)));

 

[akphidelt]

You have to add a Group By clause in there

Group By Data_ProjectComments.Project_Number

 

[JessM]

Hi -

When I add that on I get the following error message:

The LEVEL clause includes a word or argument that is misspelled or missing,
or the punctuation is incorrrect.

New Query:
SELECT Data_ProjectComments.Project_Number,
Data_ProjectComments.CurrentTime, Data_ProjectComments.CommentCode,
Data_ProjectComments.Comment
FROM Data_ProjectComments
WHERE (((Data_ProjectComments.CurrentTime)=(SELECT MAX(CurrentTime) FROM
Data_ProjectComments)));
Group By Data_ProjectComments.Project_Number

Thanks for your help with this!

 

[John Spencer]

You need to use a correlated subquery to get the latest time for each
project.

SELECT Data_ProjectComments.Project_Number
, Data_ProjectComments.CurrentTime
, Data_ProjectComments.CommentCode
, Data_ProjectComments.Comment
FROM Data_ProjectComments
WHERE Data_ProjectComments.CurrentTime=
(SELECT MAX(CurrentTime)
FROM Data_ProjectComments AS TMP
WHERE TMP.Project_Number = Data_ProjectComments.Project_Number)

A more efficient way (not updateable) might be to use a subquery in the
FROM Clause

SELECT Data_ProjectComments.Project_Number
, Data_ProjectComments.CurrentTime
, Data_ProjectComments.CommentCode
, Data_ProjectComments.Comment
FROM Data_ProjectComments INNER JOIN
(SELECT Project_Number, MAX(CurrentTime) Latest
FROM Data_ProjectComments
GROUP BY TMP.Project_Number) as Tmp
ON Data_ProjectComments.Project_Number = Tmp.Project_Number
and Data_Projects.CurrentTime = Tmp.Latest



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================