Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: Using the Max function in a totals query yields inaccurate results

    Okay, Let's say I have a table with sample data like this:

    UserID DocID Date
    12345 1 7/20/03
    12345 2 1/5/03
    12345 3 3/3/03
    98765 4 1/10/03
    98765 5 7/10/03
    98765 6 3/4/03
    35791 7 5/5/03
    35791 8 5/5/03
    35791 9 3/17/03

    I want to group by UserID with the MAX date and return the corresponding DocID. If there are two matching MAX Dates, then I want the MAX DocID. In other words, I want the following:
    UserID DocID Date
    12345 1 7/20/03
    98765 5 7/10/03
    35791 8 5/5/03

    But, what I get using a Totals Query with MAX for DocID and Date yields this (no matter which order in the query grid):
    UserID DocID Date
    12345 3 7/20/03
    98765 6 7/10/03
    35791 9 5/5/03

    I've also tried using WHERE in a separate field in the totals query (but I get an error message saying I can't have an aggregate function in the WHERE Clause, or something like that), and I have tried creating my own SQL query using HAVING (MAX(tblDoc.Date)) with no luck.

    How can I get the results I want?
    Thank you all for your expertise.

    Kevin

  2. #2
    Join Date
    Oct 2003
    Posts
    2

    I found an answer in a different forum

    Here is a link for anyone with the same question with several answers:
    http://www.mvps.org/access/queries/qry0020.htm

    Best regards,
    Kevin

Posting Permissions

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