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 (clientlist and payments). Payments is related to clientlist 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 ClientList table has fields like 'lname', 'fname', 'dob', etc, etc.

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!

[usmc-ratman]

Ok - I need to make a correction / better list out my tables and their structure, which I realized I failed to do originally... so here goes.

Table 1: "Client List"

Fields are:

ID

Lname

Fname

dob

status

Table 2: "Atty_Pmt"

Fields are:

Atty_PmtID

Date

PayAmt

PayNotes

ClientList_ID

Thanks to all,

JR