Hi, I am trying to return the last comment by projectid, when I run this query it is still giving me all comments instead of the last comment. What am I doing wrong?
SELECT tblComments.PROJECTID, tblComments.COMMENT, Max(tblComments.LASTACT_DT) AS [Last Comment Date]
GROUP BY tblComments.PROJECTID, tblComments.COMMENT
ORDER BY tblComments.PROJECTID, Max(tblComments.LASTACT_DT);
Should do the job. what I do is a using a subquery as a table, which finds maximum dates for each project ID then I JOIN this subquery with the real table by projectid and LASTACT_DT fields.
SELECT MaxDates.PROJECTID, MaxDates.MaxOfLASTACT_DT, tblComments.COMMENT
FROM (SELECT PROJECTID, Max(LASTACT_DT) AS MaxOfLASTACT_DT
GROUP BY PROJECTID
ORDER BY PROJECTID) AS MaxDates
INNER JOIN tblComments ON (MaxDates.MaxOfLASTACT_DT = tblComments.LASTACT_DT) AND (MaxDates.PROJECTID = tblComments.PROJECTID);