Access Access - Elapsed Time between Records

R

RAFarrar

Joined
Sep 11, 2013
Messages
6
Reaction score
0
I am trying to calculate the elapsed time between records as shown below and want to apply that elapsed time calculation to the first of the two records. In the example below, I would want a field ElapTim which would be 0:00:42 seconds on record one, 0:18:01 min / sec on record two, and 2:06:45 on record three. Time could be in seconds and I could convert to hours or minutes as desired. Data would be sorted by User and ascending date / Time.
Any ideas how this is done?
Thanks! Al Farrar

User Date Time Location
16494 9/10/2013 3:00:47 PM BSTRT
16494 9/10/2013 3:01:29 PM B035A
16494 9/10/2013 3:19:30 PM B063A
16494 9/10/2013 5:26:15 PM B104-W4
 
Hello! I was able to figure this out with some SQL code I found on Mr.Excel

SELECT [qry-Tech-Scans-2].WorkID, [qry-Tech-Scans-2].User, [qry-Tech-Scans-2].Time, (SELECT MIN([Time]) FROM [qry-Tech-Scans-2] AS T WHERE T.[Time]>[qry-Tech-Scans-2].[Time]) AS [Next Time], ([Next Time]-[Time])*1440 AS Minutes
FROM [qry-Tech-Scans-2]
ORDER BY [qry-Tech-Scans-2].WorkID;

Al
 
Very close now! Issue I have with the above code is that it calculates the elapsed time between different users. I only want the elapsed time to calculate when the next user record matches the current user. Any ideas what I would need to enter to make that occur?
Thanks for your help!
 
Here's what I am getting:
User Time Next Time Minutes Location
102036 8:46:30 AM 8:46:45 AM 0.25 B024B
102036 8:46:45 AM 8:48:56 AM 2.18 B024B
102036 8:48:56 AM 8:49:36 AM 0.67 B196
102036 8:49:36 AM 2:51:03 PM 361.45 B04
16494 2:51:03 PM 2:53:19 PM 2.27 BSTRT
16494 2:53:19 PM 4:19:07 PM 85.80 B029A

I don't want the calculation of elapsed time to occur when Users record changes
 
Back
Top