Max(date) not returning correct results

  • Thread starter Thread starter Asha
  • Start date Start date
A

Asha

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?

Please help.

SELECT tblComments.PROJECTID, tblComments.COMMENT, Max
(tblComments.LASTACT_DT) AS [Last Comment Date]
FROM tblComments
GROUP BY tblComments.PROJECTID, tblComments.COMMENT
ORDER BY tblComments.PROJECTID, Max
(tblComments.LASTACT_DT);
 
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?

Please help.

SELECT tblComments.PROJECTID, tblComments.COMMENT, Max
(tblComments.LASTACT_DT) AS [Last Comment Date]
FROM tblComments
GROUP BY tblComments.PROJECTID, tblComments.COMMENT
ORDER BY tblComments.PROJECTID, Max
(tblComments.LASTACT_DT);
Click to expand...

You're Grouping By COMMENT so you'll get a separate row for each
comment.

Instead, use a Subquery:

SELECT tblComments.PROJECTID, tblComments.COMMENT,
tblComments.LASTACT_DT
FROM tblComments INNER JOIN
(SELECT Max([LASTACT_DT]) FROM tblComments AS X
WHERE X.ProjectID = tblComments.ProjectID
AND X.LASTACT_DT = Max(X.[LASTACT_DT])
GROUP BY X.ProjectID);
 
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?

Please help.

SELECT tblComments.PROJECTID, tblComments.COMMENT, Max
(tblComments.LASTACT_DT) AS [Last Comment Date]
FROM tblComments
GROUP BY tblComments.PROJECTID, tblComments.COMMENT
ORDER BY tblComments.PROJECTID, Max
(tblComments.LASTACT_DT);
Click to expand...

Hi Asha,

Maybe you have already figured this out...
but did you try

SELECT
PROJECTID,
COMMENT,
LASTACT_DT As [Last Comment Date]
FROM tblComments As t1
WHERE t1.LASTACT_DT =
(SELECT Max(t2.LASTACT_DT)
FROM tblComments As t2
WHERE t2.PROJECTID = t1.PROJECTID);

This will (I believe) select all records
where LASTACT_DT is latest date
for each PROJECTID.

It may need to be adjusted if for a specific
PROJECTID, you can have more than one
record with the latest comment date (or maybe
you would want to see that if it occurs).

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Back
Top