I have a table for keeping track of dates and events, tblActivity. The fields are as such:

ActivityID - Number
ActivityDate - mm/dd/yyyy
ActivityTypeID - Number
ActivityType - Text (combo box)
ActivityChapter - Number
ActivityState - Text (combo box)

What I need to do is this; create a query or vba recordset which will find the MAX date and LAST ActivityTypeID (just in case there are multiple events on the same day). My ActivityTypeID's have been ranked 1-18 so a 9 would always show up after an 8, for instance if they occured on the same day.

So far I've created a QryLastStatusDate with the SQL of:

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

And then a QryCurrentStatus with the SQL of:

SELECT qryLastStatusDate.ActivityID, Max(qryLastStatusDate.ActivityDate) AS MaxOfActivityDate, Last(qryLastStatusDate.LastOfActivityTypeID) AS LastOfLastOfActivityTypeID, tblActivity.ActivityType, funActiveStatus([ActivityType]) AS MemberStatus
FROM tblActivity INNER JOIN qryLastStatusDate ON (tblActivity.ActivityTypeID = qryLastStatusDate.LastOfActivityTypeID) AND (tblActivity.ActivityDate = qryLastStatusDate.ActivityDate) AND (tblActivity.ActivityID = qryLastStatusDate.ActivityID)
GROUP BY qryLastStatusDate.ActivityID, tblActivity.ActivityType
ORDER BY qryLastStatusDate.ActivityID;

But this doesn't always bring back the last entry into the tblActivity for an ActivityID.

Any help here would be greatly appreciated!

Bluez

Try these two queries, which use subqueries instead of Last().

qryOne:-
SELECT *
FROM tblActivity AS a
WHERE ActivityDate in (Select max(ActivityDate) from tblActivity where ActivityID=a.ActivityID)
ORDER BY ActivityID, ActivityDate, ActivityTypeID;


qryTwo:-
SELECT *
FROM qryOne AS b
WHERE ActivityTypeID in (Select max(ActivityTypeID) from qryOne where ActivityID=b.ActivityID);


Run the second query. If it returns the correct records, add funActiveStatus([ActivityType]) AS MemberStatus to the second query.


Hope this helps.

Hi Jon,

Just got a chance to run your suggestion, and I must have not entered something right because this takes forever to run and eventually times out. I tried copying and pasting into 2 new queries and I called them qryone and qrytwo, just to see how they'd work but it doesn't seem to be right.

Any other suggestions?

Bluez

The attached DB contains my two queries. They run fine in both Access 97 and Access 2000 on my systems.

I have no idea why they didn't work on yours. But weird things do happen in Access.

Hi Jon,

I guess it's just he shear number of records that is slowing this thing down to a crawl. Is there a better way to approach this maybe? Any ideas you have would be appreciated.

Thanks again!

Bluez

It occurs to me that a concatenation of ActivityDate & ActivityTypeID can eliminate one query.

Try this new query:-

SELECT *
FROM tblActivity AS a
WHERE ActivityDate & ActivityTypeID in (Select max(ActivityDate & ActivityTypeID) from tblActivity where ActivityID=a.ActivityID)
ORDER BY ActivityID;


Hope it works.