Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Location
    West Yorkshire, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query last record (Access 2k)

    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.

  2. #2
    Star Lounger
    Join Date
    Sep 2003
    Location
    Houston, Texas, USA
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query last record (Access 2k)

    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

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query last record (Access 2k)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •