List of times in a query (1 Viewer)

p4man

New member
Local time
Yesterday, 18:21
Joined
Nov 10, 2018
Messages
10
Is it possible to do a time difference from one query?

12:05
12:07
12:10
12:15
Need to have the difference between times
12:05
12:07 02:00
12:10 03:00
12:15 05:00
So from a query need to know the difference from the last line
Hope someone can help
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:21
Joined
Oct 29, 2018
Messages
21,358
Hi. Have to run quick but do a search on "refer to previous records" using a subquery. Good luck!
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:21
Joined
Aug 30, 2003
Messages
36,118
One way

 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:21
Joined
Feb 28, 2001
Messages
27,001
It is possible, but watch out for this really big "gotcha" that Microsoft throws you. If your difference is greater than 24 hours, you won't be able to represent it directly because of the way date formats are managed by the intrinsic date conversion routines. If that IS a problem for you sometimes, you need to look up "elapsed time" threads dealing with building your own formatter.
 

p4man

New member
Local time
Yesterday, 18:21
Joined
Nov 10, 2018
Messages
10
Think I found what I need but cant get it to work
The DBguy Suggested "refer to previous records" using a subquery.
found exactly what I want (I Think)
In "Difference in query" DB looks perfect but I cant get the code right for what I have.

1580612392000.png

1580612497505.png


My Query
1580612672973.png

Changing T2 T1 to what I have is doing my head in
Times will not be greater that 24 hours
There is no need for different names A,B
Just want next column to show TimeTaken as next TimeTaken, as in value and next value
Hope you can help
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:21
Joined
May 21, 2018
Messages
8,463
Not trivival
Code:
SELECT A.timetaken, 
       A.datetaken, 
       [timetaken] + [datetaken]                 AS TimeAndDate, 
       (SELECT TOP 1 [datetaken] + [timetaken] 
        FROM   tbltest AS B 
        WHERE  A.datetaken = b.datetaken 
               AND A.timetaken > b.timetaken 
        ORDER  BY B.datetaken, 
                  b.timetaken DESC)              AS PreviousDateTime, 
       Cdate([timeanddate] - [previousdatetime]) AS Elapsed 
FROM   tbltest AS A;

Query1

timeTakendateTakenTimeAndDatePreviousDateTimeElapsed
10:00 AM​
2/1/2020​
2/1/2020 10:00:00 AM​
12:00 PM​
2/1/2020​
2/1/2020 12:00:00 PM​
2/1/2020 10:00:00 AM​
2:00:00 AM​
1:00 PM​
2/1/2020​
2/1/2020 1:00:00 PM​
2/1/2020 12:00:00 PM​
1:00:00 AM​
2:00 PM​
2/1/2020​
2/1/2020 2:00:00 PM​
2/1/2020 1:00:00 PM​
1:00:00 AM​
6:00 AM​
2/2/2020​
2/2/2020 6:00:00 AM​
8:00 AM​
2/2/2020​
2/2/2020 8:00:00 AM​
2/2/2020 6:00:00 AM​
2:00:00 AM​
12:00 PM​
2/2/2020​
2/2/2020 12:00:00 PM​
2/2/2020 8:00:00 AM​
4:00:00 AM​
 

p4man

New member
Local time
Yesterday, 18:21
Joined
Nov 10, 2018
Messages
10
Not trivival
Code:
SELECT A.timetaken,
       A.datetaken,
       [timetaken] + [datetaken]                 AS TimeAndDate,
       (SELECT TOP 1 [datetaken] + [timetaken]
        FROM   tbltest AS B
        WHERE  A.datetaken = b.datetaken
               AND A.timetaken > b.timetaken
        ORDER  BY B.datetaken,
                  b.timetaken DESC)              AS PreviousDateTime,
       Cdate([timeanddate] - [previousdatetime]) AS Elapsed
FROM   tbltest AS A;

Query1

timeTakendateTakenTimeAndDatePreviousDateTimeElapsed
10:00 AM​
2/1/2020​
2/1/2020 10:00:00 AM​
12:00 PM​
2/1/2020​
2/1/2020 12:00:00 PM​
2/1/2020 10:00:00 AM​
2:00:00 AM​
1:00 PM​
2/1/2020​
2/1/2020 1:00:00 PM​
2/1/2020 12:00:00 PM​
1:00:00 AM​
2:00 PM​
2/1/2020​
2/1/2020 2:00:00 PM​
2/1/2020 1:00:00 PM​
1:00:00 AM​
6:00 AM​
2/2/2020​
2/2/2020 6:00:00 AM​
8:00 AM​
2/2/2020​
2/2/2020 8:00:00 AM​
2/2/2020 6:00:00 AM​
2:00:00 AM​
12:00 PM​
2/2/2020​
2/2/2020 12:00:00 PM​
2/2/2020 8:00:00 AM​
4:00:00 AM​
What is tbltest
1580615650921.png

Please help
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:21
Joined
May 21, 2018
Messages
8,463
That is my demo table name. You will have to use your real table and field names. I just provided the logic.
 

p4man

New member
Local time
Yesterday, 18:21
Joined
Nov 10, 2018
Messages
10
That is my demo table name. You will have to use your real table and field names. I just provided the logic.
Any chance you could send me the tbltest (or the hole DB with query) table so I have a working copy to compare it to my not working one and figure out what has gone wrong
Thanks
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 21:21
Joined
May 21, 2018
Messages
8,463
If you want to send your table, I can help with the SQL or a function. In this case I might also consider a vba function. May be a little easier to use if you are not good at subqueries. You may also want to google other examples, since their may be other forms of the subqueries. Make sure to google Access Sql and not a pure SQL solution. Since there are other techniques not available in Access.
 

Attachments

  • TestTime.zip
    27.4 KB · Views: 83

Users who are viewing this thread

Top Bottom