Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Max function (Access 2003)

    Hi!
    I have a data with records that looks the same with Invoice# and Date that is different.
    I need to pick records with Max date and to show only those on Report.
    However Max function doesn't work and it picks all. Fiels datatype is Date/Time
    Please, help

  2. #2
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max function (Access 2003)

    Yeah...Max date per client.
    Can do it within Access Report if it is faster.

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

    Re: Max function (Access 2003)

    If I understand your description correctly, you have several dates for the same invoice number, and you want only the record with the most recent date for each invoice number.

    Create a query based on your table. Add the invoice number and Date fields - no others, even if you need them eventually.
    Select View | Totals to change the query to a totals query.
    Leave the Total option for Invoice number as Group By, and set that for Date to Max.
    This query returns the most recent date for each invoice number.
    Save this query.

    If you need to display other fields as well, you must create a new query, based on the table and on the query you just saved. Join them on Invoice number and on Date vs MaxOfDate. Add all fields from the table that you want to display to the query grid. You can use this query as record source for a form or report.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Max function (Access 2003)

    Brilliant, THANKS!

Posting Permissions

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