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

 

[Pat Hartman]

The query is performing correctly. You are asking it to return the Max ActivityDate and the Last ActivityType for each ActivityID. That is not the same is asking for the ActivityType assocated with the Max ActivityDate. The aggregate functions are operating separately within the constraint of ActivityID.

If you want the ActivityType associated with the Max ActivityDate, you'll need to break the query into 2 queries as Drevlin suggested.

qryLastStatusDate:
SELECT tblActivity.ActivityID, Max(tblActivity.ActivityDate) AS MaxActivityDate
FROM tblActivity
GROUP BY tblActivity.ActivityID;

qryLastActivity:
SELECT q.ActivityID, q.MaxActivityDate, t.ActivityType
From qryLastStatusDate as q INNER JOIN tblActivity as t ON q.ActivityID = t.ActivityID AND q.MaxActivityDate = t.ActivityDate
Order By q.ActivityID;

The problem arises when multiple activities can occur on a single day. The Last() function is much misunderstood. When people use it, they are thinking that they want the last thing that happened. However, Last() actually returns the last physical value that happens to be in a recordset which may or may not be the last activity that actually took place. Physical order has no meaning in a relational table. Records are not related to each other by the order in which they appear. Recordsets (tables and queries) are unordered sets of data (unless they are specifically sorted by a unique identifier) and the same unordered query executed multiple times, is NOT guarenteed to return identically ordered recordsets.

If ActivityDate also includes time, the above queries will work as you intend. If not, there is no reliable way to obtain the "last" activity for a particular day unless ActivityType is numeric and ALWAYS assigned in numeric order. ie ActivityType 2 follows 1, and 3 follows 2. If that is the case, then you can change the second query to include another Max():

qryLastActivity:
SELECT q.ActivityID, q.MaxActivityDate, Max(t.ActivityType) AS MaxActivityType
From qryLastStatusDate as q INNER JOIN tblActivity as t ON q.ActivityID = t.ActivityID AND q.MaxActivityDate = t.ActivityDate
Order By q.ActivityID
Group By q.ActivityID, q.MaxActivityDate;

 

[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