I have a database with payment details for members. Each member can
have multiiple payment records. What I would like to query for is the
most recent payment record for each member. Any help would be greatly
appreciated.

Using Access 2002

jording (AT) pacbell (DOT) net (jording (AT) pacbell (DOT) net) wrote in message news:<a38ea64f.0310071428.74c6ca6 (AT) posting (DOT) google.com>...
What fields are in this table?
Is there a unique identifier, a date/time field, etc?

If say you had an autonumber field as the unique identifier you could
simply do a SELECT Max(unique_identifier_field) FROM table WHERE
employee_id = XXX

It would all depend on how you've structured you database, if you have
all the members in one table and the all their payments in another
with a member reference no., then you'd need to create to querys:

query one get all the membersIDs, and their last payment date.:

SELECT Max(paymentstable.PaymentDate) AS MaxPaymentDate,
paymentstable.MemberId
FROM paymentstable
GROUP BY paymentstable.MemberId;

query two, get the details of the Member and payments by joining the
Members, Payments tables together and query one to the payments table:

SELECT MembersTable.MemberName, PaymentsTable.PaymentDate,
PaymentsTable.PaymentAmount
FROM Query1 INNER JOIN (MembersTable INNER JOIN PaymentsTable ON
MembersTable.Id = PaymentsTable.MemberId) ON (Query1.MaxOfPaymentDate
= PaymentsTable.PaymentDate) AND (Query1.MemberId =
PaymentsTable.MemberId);

you should be able copy this above into the SQL view of a query then
change the field and table names to match yours.
I hope this helps....
Philippa ;o)

jording (AT) pacbell (DOT) net (jording (AT) pacbell (DOT) net) wrote in message news:<a38ea64f.0310071428.74c6ca6 (AT) posting (DOT) google.com>...