Summarizing time frames

[DaveSwanton77]

I have a table which lists calls accepted at a call centre with the time and date stored in a field.

What I want to do is to create a query that will sum the total number of calls for each 5 minute period.

e.g 07:56:30 07:58:22 07:59:01 would equal 3 within the time period 07:55:01 and 08:00:00 ,etc.

TIA

Cheers
Dave

 

[Norie]

Does it need to be 5 minutes?

You could use this expression for 10 minutes.

TimeFrame:TimeSerial(Hour([CallTime]),Minute([CallTime])-Minute([CallTime]) Mod 10,0)

This would change your times like this

CallTime--->TimeFrame

07:56:30--->07:50:00
07:58:22--->07:50:00
07:59:01--->07:50:00

You could now group and count on TimeFrame.

 

[DaveSwanton77]

Thanks Norie

I changed the 10 to 5 after Mod and it seems to work ok.

Cheers

Dave

 

[Norie]

Are you sure it works correctly?

Check by creating a query with the call time in one column and the expression in the other.

Then you can check it's returning the correct value.

When I tried it with mod 5 it didn't seem to be giving the
right answer. Though that was on a small(tiny) dataset.