Average hours/minutes

[Keith]

Trying to average hours/minutes and I'm getting an error on my query for data
mismatch...I have a query which gives me the orders by Rep in hours/minutes
represented by column i. i: [tti]\60 & Format([tti] Mod 60,"\:00"

Example:

REP I
John 2:00
John 1:20
John 1:30
Sue 00:30
Sue 00:20

I want my results to be

REP # of orders Avg
John 3 1:40
Sue 2 0:25

Thanks for any help.

 

[Ken Snell \(MVP\)]

What data type is the tti field? What data are stored in the tti field?

 

[Ken Sheridan]

You'll need to apply the formatting to the averages of the original values in
the table, which I take it to be in minutes. You cannot average the return
values of your expression for the column i as this is a string expression,
hence the data mismatch error. Use something along these lines:

SELECT Rep,
AVG(tti)\60 & FORMAT(AVG(tti) MOD 60,"\:00") AS AverageTime
FROM YourOrdersTable
GROUP BY Rep;

Ken Sheridan
Stafford, England

 

[Keith]

Hello Ken,
The tti field is the "datediff" of two fields, ie. 199, 25, etc.
I don't have a data type set on this field, should I?

-Keith

What data type is the tti field? What data are stored in the tti field?

--

Ken Snell
<MS ACCESS MVP>

Trying to average hours/minutes and I'm getting an error on my query for
data
mismatch...I have a query which gives me the orders by Rep in
hours/minutes
represented by column i. i: [tti]\60 & Format([tti] Mod 60,"\:00"

Example:

REP I
John 2:00
John 1:20
John 1:30
Sue 00:30
Sue 00:20

I want my results to be

REP # of orders Avg
John 3 1:40
Sue 2 0:25

Thanks for any help.

 

[Keith]

There it is. Thanks Ken!

You'll need to apply the formatting to the averages of the original values in
the table, which I take it to be in minutes. You cannot average the return
values of your expression for the column i as this is a string expression,
hence the data mismatch error. Use something along these lines:

SELECT Rep,
AVG(tti)\60 & FORMAT(AVG(tti) MOD 60,"\:00") AS AverageTime
FROM YourOrdersTable
GROUP BY Rep;

Ken Sheridan
Stafford, England

Trying to average hours/minutes and I'm getting an error on my query for data
mismatch...I have a query which gives me the orders by Rep in hours/minutes
represented by column i. i: [tti]\60 & Format([tti] Mod 60,"\:00"

Example:

REP I
John 2:00
John 1:20
John 1:30
Sue 00:30
Sue 00:20

I want my results to be

REP # of orders Avg
John 3 1:40
Sue 2 0:25

Thanks for any help.