Page 1 of 4 1234 LastLast
Results 1 to 15 of 48
  1. #1
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49

    Find last Date/Time before value in another field changes

    I would like to find the last date/time for each unique Identification # before the LOCATION changes. For example...



    I have Identification # 100 that repeats 10 times on 1/1/15 at Location 15. Later in the day on 1/1/15, Identification # 100 switches locations to Location 25 where it remains until 2/1/16. On 2/1/16, Identification # 100 switches from location 25 to location 10.

    I would like to only see the last date/time from location 10 on 1/1/15, the last date/time from location 25 on 2/1/16, and so on. Below is my SQL code.


    SELECT Step2_Table.Identification, [MasterData].RecordDateAndTime, [MasterData].LOCATION
    FROM Step2_Table INNER JOIN [MasterData] ON Step2_Table.Identification = [MasterData].Identification
    WHERE ((([MasterData].DetectionDate) Between [MinOfLastDate] And [MaxOfFirstDate]));

    Here is an example of my output....


    Identification Date/Time Location
    100 11/22/13 14:28 10
    100 11/22/13 14:29 10
    100 11/22/13 14:30 10
    100 11/22/13 14:31 10
    100 11/22/13 14:32 10
    100 11/22/13 14:43 9
    100 11/22/13 14:52 9
    100 11/22/13 14:52 10
    100 11/22/13 16:10 9
    100 11/22/13 16:15 9
    100 11/22/13 16:19 9
    100 11/22/13 16:20 9
    100 11/22/13 16:27 9
    100 11/22/13 16:29 9
    100 11/22/13 16:31 9
    100 11/22/13 16:59 9
    100 11/22/13 17:27 12
    100 11/22/13 17:55 12
    For this example, I would like the output to be

    Identification Date/Time Location
    100 11/22/13 14:32 10
    100 11/22/13 14:52 9
    100 11/22/13 14:52 10
    100 11/22/13 16:59 9
    100 11/22/13 18:23 12



    I appreciate the help
    Last edited by matt704; 01-18-2017 at 11:40 AM. Reason: Added example

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not sure how to help - your SQL code contains variables which are unknown, is MasterData a query? Where do Min and Max dates come from? Also, your output example differs from your description above, it is not showing one line per "the last date/time from location 10".

    Try making one query with the three fields, group on Identification and Location, and get the Max of the date - that would show "the last date/time from location 10".

  3. #3
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    I apologize. I was running a query off a make table query and was using other background sorting. Hopefully this table helps. If you had these 3 fields, how would you write a query so that in a sequence where the location does not change, only the last date/time is returned? If you were given the data in the table below



    Identification DateAndTime Location
    100 11/22/13 13:04 10
    100 11/22/13 13:41 10
    100 11/22/13 14:10 10
    100 11/22/13 14:12 10
    100 11/22/13 14:14 10
    100 11/22/13 14:14 10
    100 11/22/13 14:16 10
    100 11/22/13 14:16 10
    100 11/22/13 14:17 10
    100 11/22/13 14:18 10
    100 11/22/13 14:19 10
    100 11/22/13 14:20 10
    100 11/22/13 14:21 10
    100 11/22/13 14:22 10
    100 11/22/13 14:23 10
    100 11/22/13 14:24 10
    100 11/22/13 14:25 10
    100 11/22/13 14:26 10
    100 11/22/13 14:27 10
    100 11/22/13 14:28 10
    100 11/22/13 14:29 10
    100 11/22/13 14:30 10
    100 11/22/13 14:31 10
    100 11/22/13 14:32 10
    100 11/22/13 14:43 9
    100 11/22/13 14:52 9
    100 11/22/13 14:52 10
    100 11/22/13 16:10 9
    100 11/22/13 16:15 9
    100 11/22/13 16:19 9
    100 11/22/13 16:20 9
    100 11/22/13 16:27 9
    100 11/22/13 16:29 9
    100 11/22/13 16:31 9
    100 11/22/13 16:59 9
    100 11/22/13 17:03 9
    100 11/22/13 17:15 9
    100 11/22/13 17:17 9
    100 11/22/13 18:20 9
    100 11/22/13 18:26 9
    100 11/23/13 6:53 9
    100 11/23/13 6:54 9
    100 11/23/13 6:57 9
    100 11/23/13 6:59 9
    100 11/23/13 7:08 9
    100 11/23/13 7:29 9
    100 11/23/13 7:32 9
    100 11/23/13 7:33 9
    100 11/24/13 0:27 9
    100 11/24/13 0:40 9
    100 11/24/13 0:41 9
    100 11/24/13 0:43 9
    100 11/24/13 6:30 9
    100 11/24/13 15:09 9
    100 11/24/13 15:11 9
    100 11/24/13 15:14 9
    100 11/24/13 15:30 9
    100 11/24/13 17:12 10
    100 11/26/13 4:37 9
    100 11/26/13 4:42 9
    100 11/26/13 5:53 9
    100 11/26/13 6:08 9
    100 11/26/13 6:11 9
    100 11/26/13 6:15 9
    100 11/26/13 6:24 9
    100 11/26/13 6:26 9
    100 11/26/13 17:36 9
    100 11/26/13 17:38 9
    100 11/27/13 2:27 9
    100 11/27/13 3:41 9
    100 11/27/13 3:43 9
    100 11/27/13 3:44 9
    100 11/27/13 3:49 9

    , I would like the output to be

    Identification DateAndTime Location
    100 11/22/13 14:32 10
    100 11/22/13 14:52 9
    100 11/22/13 14:52 10
    100 11/24/13 15:30 9
    100 11/24/13 17:12 10
    100 11/27/13 3:49 9

    The output could exclude the time(date only) but I want to make sure the locations stay in order based on how they appeared in the time sequence.



    For example, I would not want the output below because location 10 occurred prior to location 9 on 11/22/13

    Identification Date Location
    100 11/22/2013 9
    100 11/22/2013 10
    100 11/22/2013 10
    100 11/24/2013 9
    100 11/24/2013 10
    100 11/27/2013 9


    Thanks in advance.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Oh, right. I don't see how this is possible with queries, you need to write a VBA routine which reads this record by record. Do you know VBA?

  5. #5
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    Unfortunately not. How difficult would this be? Any chance you could help? Otherwise I'll be exporting to Excel...

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Sure I can help. There is one problem, however, you have two records with the same time - which one came first? The time needs to be stored on the table with seconds at least, otherwise there is no way to tell. Can you post your db with some data? Just a sample as you have here.

  7. #7
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    not sure how to post the DB? there are seconds in the records, I just didn't paste properly. If this table doesn't work for you, let me know how to post the DB. Thanks for the help!

    Identification DateAndTime Location
    100 11/22/2013 13:04:26 10
    100 11/22/2013 13:41:35 10
    100 11/22/2013 14:10:14 10
    100 11/22/2013 14:12:05 10
    100 11/22/2013 14:14:09 10
    100 11/22/2013 14:14:59 10
    100 11/22/2013 14:16:04 10
    100 11/22/2013 14:16:41 10
    100 11/22/2013 14:17:21 10
    100 11/22/2013 14:18:07 10
    100 11/22/2013 14:19:05 10
    100 11/22/2013 14:20:29 10
    100 11/22/2013 14:21:43 10
    100 11/22/2013 14:22:36 10
    100 11/22/2013 14:23:50 10
    100 11/22/2013 14:24:43 10
    100 11/22/2013 14:25:55 10
    100 11/22/2013 14:26:46 10
    100 11/22/2013 14:27:54 10
    100 11/22/2013 14:28:36 10
    100 11/22/2013 14:29:27 10
    100 11/22/2013 14:30:36 10
    100 11/22/2013 14:31:20 10
    100 11/22/2013 14:32:15 10
    100 11/22/2013 14:43:00 9
    100 11/22/2013 14:52:00 9
    100 11/22/2013 14:52:32 10
    100 11/22/2013 16:10:00 9
    100 11/22/2013 16:15:00 9
    100 11/22/2013 16:19:00 9
    100 11/22/2013 16:20:00 9
    100 11/22/2013 16:27:00 9
    100 11/22/2013 16:29:00 9
    100 11/22/2013 16:31:00 9
    100 11/22/2013 16:59:00 9
    100 11/22/2013 17:03:00 9
    100 11/22/2013 17:15:00 9
    100 11/22/2013 17:17:00 9
    100 11/22/2013 18:20:00 9
    100 11/22/2013 18:26:00 9
    100 11/23/2013 6:53:00 9
    100 11/23/2013 6:54:00 9
    100 11/23/2013 6:57:00 9
    100 11/23/2013 6:59:00 9
    100 11/23/2013 7:08:00 9
    100 11/23/2013 7:29:00 9
    100 11/23/2013 7:32:00 9
    100 11/23/2013 7:33:00 9
    100 11/24/2013 0:27:00 9
    100 11/24/2013 0:40:00 9
    100 11/24/2013 0:41:00 9
    100 11/24/2013 0:43:00 9
    100 11/24/2013 6:30:00 9
    100 11/24/2013 15:09:00 9
    100 11/24/2013 15:11:00 9
    100 11/24/2013 15:14:00 9
    100 11/24/2013 15:30:00 9
    100 11/24/2013 17:12:54 10
    100 11/26/2013 4:37:00 9
    100 11/26/2013 4:42:00 9
    100 11/26/2013 5:53:00 9
    100 11/26/2013 6:08:00 9
    100 11/26/2013 6:11:00 9
    100 11/26/2013 6:15:00 9
    100 11/26/2013 6:24:00 9
    100 11/26/2013 6:26:00 9
    100 11/26/2013 17:36:00 9
    100 11/26/2013 17:38:00 9
    100 11/27/2013 2:27:00 9
    100 11/27/2013 3:41:00 9
    100 11/27/2013 3:43:00 9
    100 11/27/2013 3:44:00 9
    100 11/27/2013 3:49:00 9
    100 11/27/2013 10:59:00 9
    100 11/27/2013 11:28:00 9
    100 11/27/2013 12:07:00 9
    100 11/27/2013 12:34:00 9
    100 11/29/2013 14:03:10 10
    100 11/30/2013 8:23:00 9
    100 11/30/2013 9:55:00 9
    100 11/30/2013 9:59:00 9
    100 11/30/2013 18:02:00 9
    100 11/30/2013 22:13:00 9
    100 12/1/2013 16:55:00 9
    100 12/2/2013 8:24:00 9
    100 12/2/2013 8:36:00 9
    100 12/2/2013 8:37:00 9
    100 12/2/2013 13:18:00 9
    100 12/2/2013 13:22:00 9
    100 12/2/2013 13:24:00 9
    100 12/2/2013 13:27:00 9
    100 12/2/2013 13:30:00 9
    100 12/3/2013 20:50:00 9
    100 12/3/2013 20:51:00 9
    100 12/3/2013 20:53:00 9
    100 12/3/2013 20:56:00 9
    100 12/3/2013 20:58:00 9
    100 12/3/2013 22:37:00 9
    100 12/3/2013 23:48:00 9
    100 12/4/2013 0:16:00 9
    100 12/4/2013 0:17:00 9
    100 12/4/2013 0:18:00 9
    100 12/4/2013 0:21:00 9
    100 12/4/2013 0:26:00 9
    100 12/4/2013 0:28:00 9
    100 12/4/2013 18:30:00 9
    100 12/5/2013 6:57:00 9
    100 12/5/2013 21:26:00 9
    100 12/5/2013 21:27:00 9
    100 12/5/2013 21:35:00 9
    100 12/6/2013 11:16:00 9
    100 12/6/2013 11:39:00 9
    100 12/6/2013 11:43:00 9
    100 12/6/2013 11:55:00 9
    100 12/6/2013 15:43:00 9
    100 12/6/2013 15:47:00 9
    100 12/7/2013 0:47:00 9
    100 12/7/2013 0:52:00 9
    100 12/7/2013 0:55:00 9
    100 12/7/2013 5:49:00 9
    100 12/7/2013 6:08:00 9
    100 12/7/2013 7:04:00 9
    100 12/7/2013 7:05:00 9
    100 12/7/2013 19:56:00 9
    100 12/8/2013 21:04:00 9
    100 12/10/2013 3:16:00 9
    100 12/10/2013 3:35:00 9
    100 12/10/2013 3:40:00 9
    100 12/10/2013 5:40:00 9
    100 12/10/2013 5:59:00 9
    100 12/10/2013 12:39:00 9
    100 12/10/2013 16:19:00 9
    100 12/11/2013 3:10:00 9
    100 12/11/2013 3:14:00 9
    100 12/11/2013 3:15:00 9
    100 12/11/2013 3:16:00 9
    100 12/11/2013 3:17:00 9
    100 12/11/2013 3:22:00 9
    100 12/11/2013 3:23:00 9
    100 12/11/2013 3:30:00 9
    100 12/11/2013 3:31:00 9
    100 12/11/2013 3:46:00 9
    100 12/11/2013 3:52:00 9
    100 12/11/2013 3:55:00 9
    100 12/11/2013 3:59:00 9
    100 12/11/2013 4:00:00 9
    100 12/11/2013 4:02:00 9
    100 12/11/2013 4:05:00 9
    100 12/11/2013 23:43:00 9
    100 12/11/2013 23:46:00 9
    100 12/12/2013 2:17:00 9
    100 12/12/2013 3:34:00 9
    100 12/12/2013 12:25:00 9
    100 12/12/2013 12:31:00 9
    100 12/12/2013 18:00:00 9
    100 12/18/2013 8:47:00 9
    100 12/18/2013 8:58:00 9
    100 12/19/2013 7:57:00 9
    100 12/19/2013 13:43:00 9
    100 12/19/2013 13:48:00 9
    100 1/10/2014 1:56:11 10
    100 1/10/2014 5:33:53 10
    100 1/10/2014 17:04:34 10
    100 1/10/2014 17:10:22 10
    100 1/10/2014 17:11:27 10
    100 1/10/2014 17:13:33 10
    100 1/10/2014 17:37:38 10
    100 1/10/2014 17:43:04 10
    100 1/10/2014 17:45:52 10
    100 1/10/2014 17:46:50 10
    100 1/10/2014 17:53:00 10
    100 1/10/2014 18:36:44 10
    100 1/12/2014 13:11:00 9
    100 1/12/2014 22:24:18 10
    100 1/13/2014 3:44:45 10
    100 1/13/2014 7:34:37 10
    100 1/13/2014 8:20:25 10
    100 1/13/2014 12:45:08 10
    100 1/13/2014 12:45:48 10
    100 1/13/2014 12:46:35 10

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Make a copy of your database
    remove everything except this data (if it is a lot, or maybe just the biggest, or leave it all if it is not too big)
    compact/repair
    zip it
    go to "Go Advanced" here when you reply
    click on the attachment icon

  9. #9
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I can give you the code but you would have to make changes to it, in particular the field names.

  10. #10
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Might be a good exercise for you to get this working!
    Attached Files Attached Files

  11. #11
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49

    attached sample DB

    TestDB1.accdb Let me know if this works

  12. #12
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    There is a function called GetLastDt(). When you need to refresh this data then it has to be run, so before a report or form or whatever. If you are using macros then the action would be RunCode, if you are using VBA then the code would be Call GetLastDt(). Link the tmp table into whatever query you are using to display data, if you need other fields.

    It may give warning messages, to switch them off go to File>Options>Client Settings>Confirm.
    Attached Files Attached Files

  13. #13
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    Thanks, Looks like this will work. I'll be testing it this afternoon/tomorrow and will let you know how it goes. I appreciate the help.

  14. #14
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    I think I understand how to run this but each time I run I get "Run-Time error '3265': Item not found in this collection." When I run the debug, this line of code is highlighted "HoldDate = rst!DateOf". Whats going on here?

  15. #15
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    rst!DateOf is my made-up name. "rst" is the recordset - look in the "Set rst=" statement, it tells you what the recordset is. That recordset has field names, they must match the code. Change it to whatever the date field is called.

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 07-16-2016, 11:38 AM
  2. Replies: 6
    Last Post: 04-19-2016, 03:58 PM
  3. Replies: 7
    Last Post: 09-02-2014, 08:18 AM
  4. Subtracting a date/time field from a number to get date/time
    By Lifeseeker1019 in forum Programming
    Replies: 4
    Last Post: 03-28-2014, 12:59 PM
  5. Replies: 5
    Last Post: 03-19-2014, 02:31 PM

Tags for this Thread

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