Average hours/minutes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Keith - 03 Feb 2008 19:56 GMT

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.

Reply to this Message

Ken Snell (MVP) - 03 Feb 2008 20:51 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> Trying to average hours/minutes and I'm getting an error on my query for
> data
[quoted text clipped - 18 lines]
>
> Thanks for any help.

Reply to this Message

Keith - 03 Feb 2008 21:08 GMT

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?
>
[quoted text clipped - 20 lines]
> >
> > Thanks for any help.

Reply to this Message

Ken Sheridan - 03 Feb 2008 21:08 GMT

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
[quoted text clipped - 16 lines]
>
> Thanks for any help.

Reply to this Message

Keith - 03 Feb 2008 21:18 GMT

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
[quoted text clipped - 29 lines]
> >
> > Thanks for any help.

Reply to this Message