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

    Last record (Access 2000)

    In a query I have a list of account numbers with payments against each account.
    What I am finding difficult to solve is extracting the LAST payment on each account.

    Thanks,

    Dan.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Last record (Access 2000)

    Do you have a date field, or a sequence number, or another way to determine which payment is the last one?

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

    Re: Last record (Access 2000)

    Yes, each payment also has the date it was made.

    Thanks for the reply regarding resetting the page numbers. It worked fine. Is there a way to do a similar thing where the page numbers are shown as page 1of 3, page 2 of 3, page 3 of 3. I am now hoping to reset these the same way.

    Thanks, Dan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Last record (Access 2000)

    Determining the last payment per account is a two-step process.

    1. Create a totals query based on the payments table with two fields: account number and payment date. Set the Total option for the first field to Group By and for the second field to Max. This query will return the date of the most recent payment for each account number. Save this query.

    2. Create a query based on the payments table and on the query you just created. Join them on the account number and on the payment date (in the query, its name probably starts with MaxOf...). Add all fields you need from the payments table to the query grid.

    Note: if there are two (or more) payments on the same most recent date for an account number, both will be returned.

    About page numbers in a report:
    It is better to ask follow-up questions in the thread itself, that way other Loungers can follow the discussion too. See <post#=303239>post 303239</post#> and its reply.

Posting Permissions

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