Results 1 to 4 of 4
  1. #1
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283

    return most recent records by date stored as text

    I have been gifted access to a table that stores employee data. Each month a new record is added for the employee. There is a Date field which is actually a text field that I cannot change in the format of 20160601 (yyyy/mm/dd) The date is always the first of the month.



    I am trying to find a way to return only the most recent record for each employee based off of this field. So if the employee is still with the company it would pull back the current month record and if an employee left 2 months ago it would return their record from 2 months ago as it is the most recent record for that employee.

    I have tried man, min, first , last to no avail.

    Has anyone done this before. Your help as always is appreciated.

    Table Structure

    Emp_no Short Text
    Last_name Short Text
    First_name Short Text
    Industry Short Text
    Manager Short Text
    DTT Short Text
    Terr_ind Short Text
    Team_key Short Text
    Ops_mgr_last_name Short Text
    Program_code Short Text
    Country_code Short Text
    Former_Territory Short Text
    Former_Program Short Text
    Date Short Text

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Since the date is formatted as yyyymmdd, it will still sort properly using MAX.
    You can do this in a series of two queries.

    The first query simply finds the maximum date for each employee.
    So, only add the Emp_No and Date fields to the query.
    Then, click the Totals button to make this an Aggregate Query.
    On the Totals Row under the Date field, change the phrase "Group By" to "Max"
    This will give you the latest date for each Emp_no.

    Now, create a second query which links your original table and your query above on both the Emp_No and Date fields.
    Then return any fields you want from the original query.

    This should give you what you want.

  3. #3
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    thank you so much...this is awesome

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome!
    Glad I could help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 09-25-2015, 10:26 PM
  2. Replies: 5
    Last Post: 09-22-2015, 12:18 PM
  3. Replies: 3
    Last Post: 03-11-2014, 07:32 PM
  4. Replies: 5
    Last Post: 02-27-2014, 08:25 PM
  5. Return most recent entry for each item
    By GenAp in forum Queries
    Replies: 1
    Last Post: 02-04-2010, 05:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums