adding time

[pleg12345]

I have got an orders database which contains a start time and an end time.

I want to work out the average it takes each employee to complete an order. An employee may make several orders a day.

The first step was completing the difference between the start time and the end time which i entered:
Diff: Format([TimeofOrder]-1-[TimeofOrderFin), "Short Time&quot

Which works accurately.
It displays
Chris 00:01
Chris 00:05


The problem is how do i add together the difference i have worked out for each employee?

 

[TonyJollans]

Hi pleg12345,

A slightly unusual bit of time calculation - what is wrong with the simpler [TimeofOrderFin]-[TimeofOrder]?

To get the averages by employee, use the average function and group by employee ..

[blue]SELECT   [Employee], 
         Format(Avg([TimeofOrderFin]-[TimeofOrder]),"Short Time") 
FROM     [Orders] 
GROUP BY [Employee];[/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Edski]

Ah yes, you've found out that you can't Sum a Date/Time field. You have to convert them to seconds, minutes and hours (and days I guess if it takes that long). Once you've done that you could just Group By employee and Sum the seconds, minutes....
Hope that helps!

 

[TonyJollans]

Edski,

What do you mean?

you can't Sum a Date/Time field.

Of course you can! There is absolutely no need to break them down into bits.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.