List of times in a query

[p4man]

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]

Hi. Have to run quick but do a search on "refer to previous records" using a subquery. Good luck!

 

[pbaldy]

One way

Microsoft Access tips: Subquery basics

Using subqueries in Microsoft Access. What subqueries are, and examples of using subqueries.

allenbrowne.com

 

[The_Doc_Man]

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.

 

[vba_php]

this problem reminds me of this from long ago:

https://www.access-programmers.co.uk/forums/threads/vertical-data-manipulation-in-queries.189359/

 

[p4man]

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)

Subquery problem

Hello I have the attached database where i used a subquery to calculate the difference between the current record and the previous one The problem that i can't add records to the form anymore ... Is there a way to solve that ?? and if the answer is No ... is there any alternative method to...

www.access-programmers.co.uk

In "Difference in query" DB looks perfect but I cant get the code right for what I have.

My Query

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

 

[MajP]

Not trivival

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

timeTaken	dateTaken	TimeAndDate	PreviousDateTime	Elapsed
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]

Not trivival

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

timeTaken	dateTaken	TimeAndDate	PreviousDateTime	Elapsed
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

Please help

 

[MajP]

That is my demo table name. You will have to use your real table and field names. I just provided the logic.

 

[p4man]

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]

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.