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

    Max Date is ignored by query (Access2000-03)

    Typo in subject corrected by HansV

    I am feeling stupid again! (what else is new right?)
    I am writing a query for the Report.
    Every ID has 2 types and dates.
    Like this
    ID Type Date
    123 B 10/1/2006
    123 A 10/1/2007

    I need to extract the record with MAX Date and somehow it would not do it.

    I had created a table with only Max Date but when I am linking right/left joing - it pulls everything anyway.

    Please, help.

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

    Re: Max Date is ignored by query (Access2000-03)

    Create a query based on the table.
    Add the ID and Date fields, but not the Type field.
    Select View | Totals.
    Set the Total option for the Date column to Max.
    Save this query as qryMaxDate. It returns the most recent date for each ID.

    Create a new query. Add both the table and the query qryMaxDate to the query grid.
    Join the table and query on ID vs ID, and also on Date vs MaxOfDate (so there will be two lines joining them).
    Add the item with the asterisk * from the table (not from the query) to the query grid.
    Save this query, and use it as Record Source for your report.

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

    Re: Max Date is ignored by query (Access2000-03)

    Of course, you didn't specify that you had such records.
    How do we determine which record should be returned for an ID if there are no dates?

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

    Re: Max Date is ignored by query (Access2000-03)

    THANKS YOU!!!!!!!!!

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

    Re: Max Date is ignored by query (Access2000-03)

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Max Date is ignored by query (Access2000-03)

    It is OK - figured out. Works! Thank you!

Posting Permissions

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