query last record (Access 2k)

Topic

New Reply

I have a query containing a list of account numbers with payments against them and the account balance which changes on each new payment. The list also has a alpha/numeric system time stamp which increases each time a new payment is made.
What I want to do is identify the last account balance on each account so this can be used on a statement.

Reply | Quote

Replies

Use a totals query looking for the last payment. That is if you have the payment data in a separate table. Let me know, if you need more detail.

deutsch

Reply | Quote

Use a totals query looking for the last payment. That is if you have the payment data in a separate table. Let me know, if you need more detail.

deutsch

Reply | Quote

I’d do this in two queries…

First, in a Totals query… Group on the Account number field, and use Max on the Payment Date/Time field… That should give you the most recent payment date for each account…

Then I’d make a second query with an Inner Join of the original query and the totals query, joining on account number… The join will find records in both tables where the account number data matches…. In this query select the Date/Time field from the totals query and the account balance field from the original query…

This should get you what you’re looking for… HTH

Reply | Quote

I’d do this in two queries…

First, in a Totals query… Group on the Account number field, and use Max on the Payment Date/Time field… That should give you the most recent payment date for each account…

Then I’d make a second query with an Inner Join of the original query and the totals query, joining on account number… The join will find records in both tables where the account number data matches…. In this query select the Date/Time field from the totals query and the account balance field from the original query…

This should get you what you’re looking for… HTH

Reply | Quote