Query on Duration

[ken]

I am needing some help finding the average duration time is spent on a
specific task.
I am using the query below to generate the month and the average duration
with success. What I now need is the avg duration of time spent for specific
calls. I am tryimg to get some thing like this
Month/Year Sev1 Sev2 Question Other Total
Marchl 2004 0:00 4:00 4:00 0:00 4:00
The average duration of severity1 will be under Sev1, Avg Sev2 under.... and
the Total being the average of all calls.All data is stored in the same
table with the following fields: ID, opendate,ClosedDate,Sev1, Sev2 and
Calltyp. Question and Other
are two items that are in the CallTyp field.

SELECT Format(Calls.OpenDate,"yyyy-mm") AS Expr1,
Format(Avg([Opendate]-[Closeddate]),"Short Time") AS Duration
FROM calls
GROUP BY Format(Calls.OpenDate,"yyyy-mm"), Month([OpenDate])
ORDER BY Month([OpenDate]);

Thanks for all the help
Ken

 

[John Vinson]

I am needing some help finding the average duration time is spent on a
specific task.
I am using the query below to generate the month and the average duration
with success. What I now need is the avg duration of time spent for specific
calls. I am tryimg to get some thing like this
Month/Year Sev1 Sev2 Question Other Total
Marchl 2004 0:00 4:00 4:00 0:00 4:00
The average duration of severity1 will be under Sev1, Avg Sev2 under.... and
the Total being the average of all calls.All data is stored in the same
table with the following fields: ID, opendate,ClosedDate,Sev1, Sev2 and
Calltyp. Question and Other
are two items that are in the CallTyp field.

SELECT Format(Calls.OpenDate,"yyyy-mm") AS Expr1,
Format(Avg([Opendate]-[Closeddate]),"Short Time") AS Duration
FROM calls
GROUP BY Format(Calls.OpenDate,"yyyy-mm"), Month([OpenDate])
ORDER BY Month([OpenDate]);

Thanks for all the help
Ken

Note that a Date/Time field is stored internally as a Double Float
number, a count of days and fractions of a day since midnight,
December 30, 1899. As such, it's better suited to depict a specific
point in time than a duration.

I'm not sure I understand your table structure, though! Is 4:00 a
duration of four minutes? or four hours? or what? Why are you
subtracting ClosedDate from OpenDate, and what does that difference
have to do with the (non-normalized) values in Sev1 and Sev2 and
Question?

I'd suggest that if one Call (??) has multiple phases, that you split
the phases out into a second table, storing the duration of each phase
in a Long Integer field, in minutes or seconds as appropriate. A
Totals query can then more easily sum them up. You can use an
expression like

[Duration] \ 60 & ":" & Format([Duration] MOD 60, ":00")

to display this as hours and minutes (or minutes and seconds).