Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Newest Date (Access '97)

    I have a field with multiple dates and a reference field. Each reference can have more than one date.
    How do i ask for the most recent date per reference

  2. #2
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newest Date (Access '97)

    In a query add your two fields. Turn on the Totals, GroupBy your Reference field, and set the Date Field to Max. That should do it.
    Paul

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Newest Date (Access '97)

    Do you mean you have multiple date fields in a record or that you have multiple date values stored in a single field? If it's the latter, you're entirely out of luck and your design needs to be overhauled. Or did you mean that you have multiple records with different dates in them but the same reference number?
    Charlotte

  4. #4
    New Lounger
    Join Date
    Mar 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newest Date (Access '97)

    I have multiple records with different dates in them but they can have the same reference number. I am trying to determine the most recent date for each reference number. How do i get Access to return the most recent date for each reference

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newest Date (Access '97)

    I'd go with what pbricker said
    create a query, add the table, select the reference field and date field, turn on totals, select group be for reference field, max for the date, run the query
    You should then have a list of the maximum date for each unique reference

  6. #6
    New Lounger
    Join Date
    Mar 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newest Date (Access '97)

    Using the max date option is not suitable. I have a table with over 29,000 records it is possible to have say 80 date records against the same reference, i need Access to return just the most recent date per reference because i need to extract data specific to the most recent entry on a reference. I would like to be able to tell Access the refernce number i am interested in and have the most recent date information returned to me in a one line response. Does anyone know if this is possible?

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newest Date (Access '97)

    Do what Sculsaw said and then put the following in the criteria field for reference number.

    [Enter referencenumber]

    You will then be prompted for your reference number when you run the query.

Posting Permissions

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