More help with returning the newest record in a query

  • Thread starter Thread starter JessM
  • Start date Start date
J

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
 
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;
 
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;
 
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.
 
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.
 
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);
 
Thanks - that worked!!

KARL DEWEY said:
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);
Click to expand...
 
Back
Top