Most Recent Entry Criteria?

[slocat]

I have a Membership table which has a one-to-many relationship to a MemberActivity table. The MembershipActivity table lists activity and date for each entry. I want to base certain forms and reports on the most recent activity date for each member. Is there a function for this? Thank you for any suggestions!

 

[CosmoKramer]

Did you try the Max function in your query?

Max([activity_date])

 

[slocat]

Thank you for your reply. The Max function returns the most recent date, but I need the whole record for that date. The following generates an error (Activity is not part of an aggregate function):

SELECT Activity, MAX(ActivityDate)
FROM MembActivity
WHERE MEMB_NUMB = "20686";

Could you suggest a syntax that will work? Thanks!

 

[CosmoKramer]

Try

SELECT Activity, MAX(ActivityDate)
FROM MembActivity
GROUP BY Activity
WHERE MEMB_NUMB = "20686";

Let me know if this works for you....

 

[slocat]

Thanks for sticking with me!

I tried the code above and it gave me a syntax error (missing operator) message.

The following gets no error message:

SELECT Activity, MAX(ActivityDate)
FROM MembActivity
WHERE MEMB_NUMB = "28686"
GROUP BY Activity;

but it generates the last date for each activity code for that member, which gives me multiple records. I want just the last record for any activity for the member, in other words, the most recent record for that member.

 

[SyedAqif]

Maybe This will help

SELECT Activity, ActivityDate
FROM MembActivity where ActivityDate=
(select Max(ActivityDate) from MembershipActivity
WHERE MEMB_NUMB = "28686"
And Activity= "YourActivity&quot
GROUP BY Activity;


Cheers!
Aqif

 

[slocat]

GOT IT!

Actually the Group By generates an error message, but I don't need it anyway. The following produces just what I want:

SELECT Activity, ActivityDate
FROM MembActivity
WHERE ActivityDate=(SELECT Max(ActivityDate) FROM MembActivity
WHERE MEMB_NUMB = "28686&quot

THANK YOU! I really appreciate you help.