Results 1 to 8 of 8
  1. #1
    Russellh is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    68

    Pull the Record with the Most Current Connect Date


    If I have a table with the columns
    LOCATION #...Customer #....Customer NAME...CONNECTIONDATE

    And the same location # has several records (1 record for each person who has lived there).
    How do I pull 1 record for each location and that record be the one with the most recent connectiondate.

    All Dates are in the format YYMMDD so i should just be able to use the highest number. For instance May 23, 2106 would be 160523.

    I see there is a last function or min/max function but I'm not sure how to use it here to pull all the location #s but the most recent customer for each location.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Show us a few records from this table --describe/explain the records if there is anything you haven't yet told us.
    You might also show us the record(s) you want selected from your sample.

  3. #3
    Russellh is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    68
    Ok with give data table example below
    the two records would be pulled
    8675309 6534236 Russell Hamilton 160523


    123456 4125356 Taylor Hicks 160401

    FROM
    Location # Customer # Customer Name Connection Date
    8675309 6534236 Russell Hamilton 160523
    8675309 73648723 Bob Barker 150101
    8675309 7364738 Ronald Reagan 140801
    123456 7634739 Bill Gates 140131
    123456 982376 Bill Clinton 150203
    123456 4125356 Taylor Hicks 160401

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My suggestion is to approach this in 2 steps. However, you should not use names with embedded spaces, and
    you should not use non-alpha characters in your table field and object names. I have taken your data, modified the field names, made up my own table name.

    Step 1. Create a query to find the LatestConnectedDate by Location


    Code:
    SELECT CustLocations.[Location ]
    , Max(CustLocations.ConnectionDate) AS LatestConnectionDate
    FROM CustLocations
    GROUP BY CustLocations.[Location ];
    Step 2.

    Use the query from step 1 and the table; then join these on Location and LatestConnectedDate

    see the jpg showing the design set up.

    Click image for larger version. 

Name:	DesignSetUpForStep2.jpg 
Views:	14 
Size:	31.9 KB 
ID:	24724




    Here is the result of step 2

    Location Customer CustomerName ConnectionDate
    8675309 6534236 Russell Hamilton 160523
    123456 4125356 Taylor Hicks 160401


    Good luck.

  5. #5
    Russellh is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    68
    Worked Great!! THANKS!!

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    Quote Originally Posted by Russellh View Post
    If I have a table with the columns
    LOCATION #...Customer #....Customer NAME...CONNECTIONDATE

    And the same location # has several records (1 record for each person who has lived there).
    How do I pull 1 record for each location and that record be the one with the most recent connectiondate.

    All Dates are in the format YYMMDD so i should just be able to use the highest number. For instance May 23, 2106 would be 160523.

    I see there is a last function or min/max function but I'm not sure how to use it here to pull all the location #s but the most recent customer for each location.
    I'm freehanding this, so there may be typos:

    Code:
    SELECT TABLE.* FROM TABLE, (SELECT FILTER.LOCATION, MAX(FILTER.CONNECTIONDATE) MAX_CONNECTIONDATE FROM TABLE AS FILTER) AS FILTER_CONNECTION WHERE TABLE.LOCATION = FILTER_CONNECTION.LOCATION AND TABLE.CONNECTIONDATE = FILTER_CONNECTION.MAX_CONNECTIONDATE;

  8. #8
    InsuranceGuy is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Aug 2015
    Location
    Colorado
    Posts
    126
    And then, I scroll down and there it is. Ignore me. Cheers!

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

Similar Threads

  1. Replies: 3
    Last Post: 03-02-2016, 09:39 AM
  2. Find Record with date before Current Record
    By mrmims in forum Queries
    Replies: 1
    Last Post: 10-01-2015, 08:10 AM
  3. Replies: 6
    Last Post: 12-11-2012, 09:40 PM
  4. Replies: 15
    Last Post: 04-06-2012, 10:57 AM
  5. Replies: 11
    Last Post: 03-29-2012, 11:23 PM

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