Average time between events

[shavon]

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

 

[Remou]

How about:

[tt]SELECT Avg(DateDiff("s",(Select Top 1 [Field1] from tblTable A Where a.Field1<tblTable.Field1 Order By a.Field1 desc),[Field1])) AS AnAverage
FROM tblTable;[/tt]

 

[Golom]

or

Select Avg(DateDiff("s",B.DateField, A.DateField)) As [Avg Seconds}

From tbl A, tbl B

Where B.DateField = (Select MAX(DateField) From tbl X
                     Where X.DateField < A.DateField)

 

[shavon]

Golom/Remou:

They both work superbly.

Thanks

 

[dhookom]

How about:

SELECT (Max(DateField)-Min(DateField))/Count(DateField) as TheAvg
FROM tblA;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]