Last Payment Date by Member

[Pete]

We have a table with member payments. We'd like a query
that shows when the last payment was made by each member.
I've tried Max(PayDate) and First(PayDate), but I either
get all records or I only get the first member's payment
and none of the others.
Is this possible in a Query and if so, how?
Thanks.

 

[John Vinson]

We have a table with member payments. We'd like a query
that shows when the last payment was made by each member.
I've tried Max(PayDate) and First(PayDate), but I either
get all records or I only get the first member's payment
and none of the others.
Is this possible in a Query and if so, how?
Thanks.

A Subquery can do this. Create a Query based on the Member table
joined to the Payments table; on the Criteria line under PayDate put

=(SELECT Max([PayDate]) FROM Payments AS X WHERE X.MemberID =
Members.MemberID)

using, of course, your own fieldnames and tablenames.