More 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 for each project number.

Below is what I tried, but 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

 

[KARL DEWEY]

Subqueries do not end with a semicolon. Try it this way ---
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;

 

[KARL DEWEY]

Try it this way ---
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,
Data_ProjectComments.CurrentTime, Data_ProjectComments.CommentCode,
Data_ProjectComments.Comment;

 

[JessM]

It works but it only gives me the most recent project number comment overall,
it doesn't give me the most recent comment for each project number.

 

[JessM]

I get the following error message when I try it:

You tried to execute a query that does not inlcude the specifed expression
'CurrentTime' as part of an aggregate function.

 

[KARL DEWEY]

I do not know sub queries so I used two ---
Data_ProjectComments_Latest ---
SELECT Data_ProjectComments.Project_Number,
Max(Data_ProjectComments.CurrentTime) AS MaxOfCurrentTime
FROM Data_ProjectComments
GROUP BY Data_ProjectComments.Project_Number;

SELECT Data_ProjectComments.Project_Number,
Data_ProjectComments.CurrentTime, Data_ProjectComments.CommentCode,
Data_ProjectComments.Comment
FROM Data_ProjectComments INNER JOIN Data_ProjectComments_Latest ON
(Data_ProjectComments.CurrentTime =
Data_ProjectComments_Latest.MaxOfCurrentTime) AND
(Data_ProjectComments.Project_Number =
Data_ProjectComments_Latest.Project_Number);

 

[JessM]

Thanks - that worked!!

I do not know sub queries so I used two ---
Data_ProjectComments_Latest ---
SELECT Data_ProjectComments.Project_Number,
Max(Data_ProjectComments.CurrentTime) AS MaxOfCurrentTime
FROM Data_ProjectComments
GROUP BY Data_ProjectComments.Project_Number;

SELECT Data_ProjectComments.Project_Number,
Data_ProjectComments.CurrentTime, Data_ProjectComments.CommentCode,
Data_ProjectComments.Comment
FROM Data_ProjectComments INNER JOIN Data_ProjectComments_Latest ON
(Data_ProjectComments.CurrentTime =
Data_ProjectComments_Latest.MaxOfCurrentTime) AND
(Data_ProjectComments.Project_Number =
Data_ProjectComments_Latest.Project_Number);