usmc-ratman Posted June 19, 2007 Share Posted June 19, 2007 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_PmtIDDatePayAmtPayNotesClientList_IDThe 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 helpJRSemper-Fi! Link to comment Share on other sites More sharing options...
usmc-ratman Posted June 20, 2007 Author Share Posted June 20, 2007 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:IDLnameFnamedobstatusTable 2: "Atty_Pmt"Fields are:Atty_PmtIDDatePayAmtPayNotesClientList_IDThanks to all,JR Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now