average time per day

[travismp]

i have a date field with time and date each record is entered. results look like this

Date_Complete
4/9/2007 8:26:11 AM
4/9/2007 8:31:25 AM
4/9/2007 8:34:14 AM
4/9/2007 8:34:21 AM
4/9/2007 8:34:29 AM
4/9/2007 8:34:36 AM
4/9/2007 8:34:49 AM
4/9/2007 8:41:27 AM
4/9/2007 8:41:49 AM
4/9/2007 8:42:32 AM
4/9/2007 8:42:39 AM
4/9/2007 8:42:49 AM
4/9/2007 8:43:36 AM
4/9/2007 8:44:21 AM
4/9/2007 8:45:48 AM


I want a query or report to give me the average entry time per record. Something like: Average time between orders 1:25 (one minute twenty five seconds)

 

[gemma-the-husky]

easiest way is to add the times
count the records
and find the lowest time, which you can do with a single totals query, i think


the average time is then
total times - (recordcount*lowest time)\recordcount

the time will be stored as a fraction of a day - but just format it as appropriate time format
something like mm:sss for minutes/seconds

 

[Jon K]

Try these two queries, replacing with the correct table name.

qryOne:-
SELECT Date_Complete, (Select Max(s.Date_Complete) from [TableName] as S where DateValue(s.Date_Complete)=DateValue([TableName].Date_Complete) and S.Date_Complete < [TableName].Date_Complete) AS PrevTime, Date_Complete-[PrevTime] AS TimeDiff
FROM TableName;

qryTwo:-
SELECT DateValue([Date_Complete]) AS Dates, Sum([TimeDiff])/Count(*) AS AvgTime
FROM qryOne
GROUP BY DateValue([Date_Complete]);


You can switch the second query "qryTwo" to query Design View and put hh:nn:ss in the Format property of the AvgTime column in the Field Properties sheet to display AvgTime in 0:00:00 format.

Run the second query.


Note:
Running the subquery in qryOne will take time if the table is large.

.