Average Duration Per month

  • Thread starter Thread starter ken
  • Start date Start date
K

ken

I am capturing some time in a table using the short time format of the date
field. How can I get the average duration time is spent. Time is entered as
hh:mm. Say for instance three people enter time on a call, their time would
be entered as 5:00, 6:30 and 1:30. How do I use a query to get the average
duration a person spent on this call is 4.3 hours. Also how can I capture
the total ammount of time spent on the call.

Thanks
Ken
 
Use a totals query and average the duration/time field. One recommendation
is to NOT use a date/time field for storing durations. I read 5:00 as five
o'clock and 1:30 as one-thirty. I would use a numeric field and store either
the number of hours or minutes.
 
ken said:
I am capturing some time in a table using the short time format of the date
field. How can I get the average duration time is spent. Time is entered as
hh:mm. Say for instance three people enter time on a call, their time would
be entered as 5:00, 6:30 and 1:30. How do I use a query to get the average
duration a person spent on this call is 4.3 hours. Also how can I capture
the total ammount of time spent on the call.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

DateTime data types are stored as floating point values. The integer
value is the day (since Dec 31, 1899) and the decimal value is the time
in milliseconds since midnight of the day. Therefore, your time value
is actually being stored w/ a zero as the integer value and the decimal
time value. E.g.: 6:30 AM would be stored as 0.270833333333333.

The time examples you gave seem to indicate duration, at least that is
implied by "their time would be entered as..." and "the average duration
.... is 4.3 hours" ((5+6.5+1.5)/3 = 4.333). So why are you asking how to
find their "total amount of time spent on the call"? Isn't that
duration the examples you gave?

Query try:

SELECT PersonID, Sum(Duration)/Count(*) As AvgDuration
FROM Calls
GROUP BY PersonID

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGSYcYechKqOuFEgEQLxVwCfR19/OwgfD71k//GfkOp6OIP/XH0AnjJ3
IHsca7VaEnfR+hQNxfwNiMgg
=ShI0
-----END PGP SIGNATURE-----
 
Back
Top