MAX and LAST Not Returning Correct Record

[Bluezman]

I have an SQL statement in a query called qryCurrentStatus, which is supposed to look up the MAX date and LAST type for records in tblActivity.

SELECT tblActivity.ActivityID, Max(tblActivity.ActivityDate) AS ActivityDate, Last(tblActivity.ActivityType) AS ActivityType
FROM tblActivity
GROUP BY tblActivity.ActivityID
ORDER BY tblActivity.ActivityID, Max(tblActivity.ActivityDate) DESC;

However, there are times when this doesn't seem to work correctly for all records. It appears to pick up the correct date, but the corresponding activitytype is from a different record.

I've removed the query completely and re-entered it, thinking that perhaps that would help, but I get the same results.

Any ideas?

Bluez

 

[Drevlin]

It may be slightly annoying... but you could build your query in two steps (actually two queries).
The first query would be:

SELECT tblActivity.ActivityID, Max(tblActivity.ActivityDate) AS ActivityDate
FROM tblActivity
GROUP BY tblActivity.ActivityID
ORDER BY Max(tblActivity.ActivityDate) DESC;

You could name this query qryLastStatusDate

Then build your final query:

SELECT qryLastStatusDate.ActivityID, qryLastStatusDate.ActivityDate AS ActivityDate, Last(tblActivity.ActivityType) AS ActivityType
FROM qryLastStatusDate LEFT JOIN tblActivity ON qryLastStatusDate.MaxOfActivityDate=tblActivity.ActivityDate
GROUP BY qryLastStatusDate.ActivityID, qryLastStatusDate.MaxOfActivityDate;



It's always frustrated me that you can't do this in one step. I wish there was an option in the totals column of an aggregate query that let's you pick the item that goes With the Max item of a Group.

If anybody knows how, please feel free to pass along the information.

Peace

 

[Bluezman]

Thanks to both Drevlin and Pat for their answers. Now that I see the responses, it makes perfect sense and I can use that information in many different areas in the future!

Bluez