Average Time Query

[Guest]

Hello:

I would like a query to determine the average time between events. For
example, given the following records:

TimeColumn
8/5/2006 12:15:35 AM
8/5/2006 12:15:44 AM
8/5/2006 12:15:49 AM
8/5/2006 12:15:55 AM
8/5/2006 12:16:01 AM
8/5/2006 12:16:06 AM
8/5/2006 12:16:12 AM
8/5/2006 12:16:19 AM
8/5/2006 12:16:25 AM
8/5/2006 12:16:30 AM

I want a query to do a datediff between each record and tell me the average
in seconds. Is this possible?

Thank you

 

[Marshall Barton]

I would like a query to determine the average time between events. For
example, given the following records:

TimeColumn
8/5/2006 12:15:35 AM
8/5/2006 12:15:44 AM
8/5/2006 12:15:49 AM
8/5/2006 12:15:55 AM
8/5/2006 12:16:01 AM
8/5/2006 12:16:06 AM
8/5/2006 12:16:12 AM
8/5/2006 12:16:19 AM
8/5/2006 12:16:25 AM
8/5/2006 12:16:30 AM

I want a query to do a datediff between each record and tell me the average
in seconds. Is this possible?

This won't be very fast if the table is large:

SELECT Avg(DateDiff("s",
DMax("TimeColumn", "thetable", "TimeColumn < "
& Format(TimeColumn, "\#m\/d\/yyyy h\:n\:s\#),
TimeColumn)) As AvgDelta
FROM thetable

There are probably additional criteria needed, but your
example doesn't indicate what else is going on.

 

[Peter R. Fletcher]

If that is literally all you need to do (at this particular point in
your application), I wouldn't use a Query. I would use a VBA function,
e.g.:

Function MeanTDiff() As Double

Dim rst As ADODB.Recordset
Dim dblAccDiff As Double
Dim lngCount As Long
Dim dtPrev As Date

Set rst = New ADODB.Recordset

With rst
.Open "SELECT TimeColumn FROM MyTable ORDER BY TimeColumn",
CurrentProject.Connection, adOpenStatic, adLockReadOnly
If .RecordCount <> 0 Then
.MoveFirst
dtPrev = .Fields("TimeColumn")
lngCount = 1
Do
.MoveNext
If .EOF Then Exit Do
dblAccDiff = dblAccDiff + DateDiff("s", dtPrev,
..Fields("timecolumn"))
lngCount = lngCount + 1
Loop
If lngCount = 1 Then
MeanTDiff = 0#
Else
MeanTDiff = dblAccDiff / lngCount
End If
Else
MeanTDiff = -1#
End If
.Close
End With
Set rst = Nothing

End Function

The function returns -1 if the table is empty and 0 if there is only
one row.

Watch out for the unwanted linebreaks in the Open statement and the
second dblAccDiff assignment

Hello:

I would like a query to determine the average time between events. For
example, given the following records:

TimeColumn
8/5/2006 12:15:35 AM
8/5/2006 12:15:44 AM
8/5/2006 12:15:49 AM
8/5/2006 12:15:55 AM
8/5/2006 12:16:01 AM
8/5/2006 12:16:06 AM
8/5/2006 12:16:12 AM
8/5/2006 12:16:19 AM
8/5/2006 12:16:25 AM
8/5/2006 12:16:30 AM

I want a query to do a datediff between each record and tell me the average
in seconds. Is this possible?

Thank you

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[Guest]

I got an answer that works.

SELECT 60/Avg(DateDiff("s",(Select Top 1 [thetime] from Table1 A Where
a.thetime<table1.thetime Order By a.thetime desc),[thetime])) AS AnAverage
FROM Table1

Thanks anyway.

 

[David F. Cox]

My brain might have fused, but isn't it ( max(time) - min(time) ) / (number
of events - 1)?

David F. Cox

 

[Peter R. Fletcher]

No, your brain is fine - its the rest of us who need to take our
Access blinkers off and think about the real question! That would
almost certainly execute much faster than our approaches, too.

My brain might have fused, but isn't it ( max(time) - min(time) ) / (number
of events - 1)?

David F. Cox

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher