Solved

find time gaps between records

Posted on 2014-09-10
9
181 Views
Last Modified: 2014-09-11
access 2010

I have a query result that i need to now create another query that will show me gaps between records.

I need to know if i can find the time between each each record.
What is the gap between record 2 and 1
What is the gap between record 3 and 2
etc....
???
is this possible..

maybe a pivot table ?


ActualQueryTime
9/10/2014 8:31:02 AM
9/10/2014 8:44:44 AM
9/10/2014 8:46:32 AM
9/10/2014 8:46:38 AM
9/10/2014 8:46:43 AM
9/10/2014 8:46:48 AM
9/10/2014 8:47:38 AM
9/10/2014 8:47:45 AM
9/10/2014 8:47:48 AM
9/10/2014 8:47:54 AM
9/10/2014 8:47:58 AM
9/10/2014 8:48:02 AM
9/10/2014 8:48:02 AM
9/10/2014 8:48:34 AM


Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 5
  • 3
9 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 40316314
what you want is what in Oracle and SQL Server is the "LAG" or "LEAD" functions, which return the value of the previous/next row
in ms access you don't have that function, so you must do a subquery to get that value... a domain function would also do, but is not recommended for performance reasons

select t.ActualQueryTime
  ,  ( select min(n.ActualQueryTime) from yourtable n where n.ActualQueryTime > t.ActualQueryTime ) nextTime  
  from yourtable t

and you can of course do maths on the 2 values.
hope this helps
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40316587
Or, instead of the sub query, you could do:

SELECT T1.ActualQueryTime
, Min(T2.ActualQueryTime) as NextTime
FROM yourTable as T1
LEFT JOIN yourTable as T2
ON T1.ActualQueryTime < T2.ActualQueryTime
GROUP BY T1.ActualQueryTime

Depending on the size of your recordset, I think this will run faster than the subquery method.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40317088
dale,
Would there be anyway to combine these 2 "queries now"...so i have a 3 column result set ?
SELECT T1.ActualQueryTime
, Min(T2.ActualQueryTime) as NextTime
FROM Search_Terms_Hourly as T1
LEFT JOIN Search_Terms_Hourly as T2
ON T1.ActualQueryTime < T2.ActualQueryTime
GROUP BY T1.ActualQueryTime

and then i did this:


SELECT DateDiff("n", ActualQueryTime, NextTime) AS MinutesDiff
FROM Search_Terms_Hourly_Setup1
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 400 total points
ID: 40317127
Access will generally let you use a computed column within the same query, but I don't think that will work here because of the group by.

You might try:

SELECT Temp.ActualQueryTime, Temp.NextTime,
, DateDiff("n", ActualQueryTime, NextTime) as Gap
FROM (
SELECT T1.ActualQueryTime
, Min(T2.ActualQueryTime) as NextTime
FROM Search_Terms_Hourly as T1
LEFT JOIN Search_Terms_Hourly as T2
ON T1.ActualQueryTime < T2.ActualQueryTime
GROUP BY T1.ActualQueryTime
) as Temp
WHERE DateDiff("n", Temp.ActualQueryTime, Temp.NextTime) > 2
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 40317172
Thanks very much to both !!
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40317175
Worked great Dale,
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40317229
Dale, I want to understand something, In the where clause.
does the
WHERE DateDiff("n", Temp.ActualQueryTime, Temp.NextTime) > 2

>2  stand for 2min ?

So its possible to lower to  1  or just leave out the where clause all together.
??
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40317347
Yes,  2 minutes.

As a operations analyst, I might change the DateDiff to count seconds instead of minutes.

Then you could run a query against that query to determines the average gap between records and the standard deviation, and then only select those records that fall outside some appropriate range.  Maybe that would be >65 or >70.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 40317551
very good Thanks for the additional help !
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question