Time Calculation

[RickieB]

Can someone please help me as this is driving me crazy and I know the answer must be simple.

I have a query/report that shows the duration a call has taken i.e. it times each part of the call. For example it may take a member of staff 00:00:30 to answer a call, then he puts a customer on hold whilst he speaks to another department which could be another 30 seconds, and then he ends the call with the customer.

So my report and query shows:

Ringing 00:00:30
Speaking to customer 00:04:10
On hold 00:00:30
Ends call with customer 00:02:00

The duration of the action is in a seperate column.
How do I tell access to add these up so I get a total of 00:07:10 for the entire call from start to finish?

Thanks for you help

Rich

 

[stopher]

Ringing 00:00:30
Speaking to customer 00:04:10
On hold 00:00:30
Ends call with customer 00:02:00

The duration of the action is in a seperate column.
How do I tell access to add these up so I get a total of 00:07:10 for the entire call from start to finish?

Thanks for you help

Rich

I see no reason why you can't just add them assuming they are set up at date/time datatypes.

So in a query, add somehting like the following to a new column:
TotalTime: [ringing]+[speaking]+[OnHold]+[Ending]

Obviously you have to use the correct field names. Also, you will need to format TotalTime as time.

The reason this works is despite how time might look on the screen, it's really stored as a decimal number (fractions of a 24hour day). So 12 hours will be stored as 0.5, 6hrs as 0.25 etc. The only challenge you will have is when you want to sum time over more than 24 hours because not matter how you format you won't be able to display 25:30:00 (25½ hours) for example. There are ways to deal with this though.

hth
Stopher

 

[stopher]

Thank you for your reply, however, I dont think I explained myself properly. All the actions are actually under one column, headed Actions, and the durations for the actions are under one column headed Duration. So, I need to calculate the total duration, however, the formuala that I have been using of =sum([Duration]) doesnt seem to work with time.

RickieB
I've copied this from PM you sent me. It's best to respond to the forum so that others can be assisted by the problem/solution. Also you will get a quicker response to your query.

Anyway, I've attached an example of times being summed up on a report (Report1). Are you sure the times in your database are of date/time datatype?

hth
Stopher