Access 2k query problem (print last record ?)

[usmc-ratman]

I'm running access 2k, WinXP Pro SP2 - both are current with latest updates. I'm having a problem getting the select query behind a report to give me the results I need. The query works with two tables (Client List and Atty_Pmt). Atty_Pmt is related to Client List on a one-many relationship, using ClientList_ID primary key.
The payments table structure is as follows:

Atty_PmtID​

Date​

PayAmt​

PayNotes​

ClientList_ID​

The Client List table has fields like 'lname', 'fname', 'dob', etc, etc.

The ClientList table structure is as follows:​

ID​

Lname​

Fname​

dob​

status​

Im trying to structure a query that will list each client, and ONLY the last payment they made. I tried using a select query Top n, making 'n' value 1, and it only lists one client, one payment record, which isnt even the last payment they made.
This is the query that I currently have....

SELECT TOP 1 [Client List].ID, [Client List].Lname, [Client List].Fname, Atty_Pmt.Atty_PmtID, Atty_Pmt.Date, Atty_Pmt.PayAmt, Atty_Pmt.PayNotes FROM [Client List] INNER JOIN Atty_Pmt ON [Client List].ID = Atty_Pmt.ClientList_ID;

Can someone tell me how to single out the most recent payment (either by greatest date - maybe closest date to current date - or by the 'Atty_PmtID' field). It could possibly be based on the 'Atty_PmtID' field because the payments are entered in order as they come in, so the most recent payment would be the higher 'Atty_PmtID' autonumber, but I just dont know.
Thanks in advance for your suggestions and help
JR
Semper-Fi!

 

[Moniker]

Have you considered just using an aggregate query, and use group by on everything but the date, which would be Max(YourDateField)? That should do it.

 

[usmc-ratman]

Have you considered just using an aggregate query, and use group by on everything but the date, which would be Max(YourDateField)? That should do it.

Moniker - I tried this last night, but it didn't seem to work, I edited my initial question above to include the structure of the clientList table fields that I need. Can you show me the SQL structure your talking about using the fields outlined in my post....
Thank you,
JR