Total duration

[Gasman]

Hi everyone,
Looking to see if I can summarise call logs reasonably easy in Access (for me at least).
I need to be able to sum all the individual call duration values and output as hh:mm:ss

So far I have as below and am happy to write a function if need be but feel sure this would have been done before without a custom function?
Duration for each record is in seconds.

TIA

SELECT Caller.CallerName, Format([Time],"dd/mm/yyyy") AS CallDate, Count(Format([Time],"dd/mm/yyyy")) AS Calls, Sum(BT.[Duration (s)]) AS [SumOfDuration (s)], Min(Format([Time],"hh:nn:ss")) AS FirstCall, Max(Format([Time],"hh:nn:ss")) AS LastCall
FROM Caller INNER JOIN BT ON Caller.CallerNumber = BT.[Business Number]
GROUP BY Caller.CallerName, Format([Time],"dd/mm/yyyy");

 

[jdraw]

What result does your attempt provide? What does BT represent?
Error message?
A sample showing a few records and your desired Sum would help readers understand the situation.

 

[Ranman256]

i would convert all time to seconds, sum, then convert it back to hrs,min,secs

Secs: format([date],"hh")*3600 + format([date],"nn")*60 +format([date],"ss")

 

[Gasman]

Hi jdraw,
No real attempt as yet, just got the total amount in seconds.
I have only tried

Expr1: Format([Duration (s)],"hh:nn:ss")

and have yet to find the same problem via Google.?

BT is the name of one of the phone provider. I downloaded a set of data into Excel and called the file BT.xlsx and have just linked to it for now.

I'll upload the db on Monday after sanitising the data.

I have the data working in Excel, but that involves several steps (copy/paste/refresh pivot) and as a Manager has to do this, the more I can automate it the better.

Thank you.

 

[Gasman]

Hi Ranman256,

The data is already in seconds for each call.?

Sorry, your post was seen after I replied to jdraw.

i would convert all time to seconds, sum, then convert it back to hrs,min,secs

Secs: format([date],"hh")*3600 + format([date],"nn")*60 +format([date],"ss")

 

[The_Doc_Man]

If that total call time includes multiple calls on the same issue and the total exceeds 24 hours, no format involving any variant of "hh:nn:ss" is going to work quite right. You would do better write a function to take that number of seconds and format the output as a string given the number of seconds as input.

You can use formulas such as

Seconds = TotalSeconds MOD 60
Minutes = ( TotalSeconds MOD 3600 ) / 60
Hours = ( TotalSeconds / 3600 )

Then use a format on each part and concatenate the parts, perhaps with a colon as a delimiter. I'll bet if you search the form for "Formatting elapsed time" you will find a few code samples, so I won't provide one here.

 

[Gasman]

Hi Doc,
It will never exceed (or at least what I am measuring now) 24 hours. All my bosses want to see is how long certain colleagues are on the phone.
I have managed to do it since posting bu using two queries, one to sum the seconds as per Ranman256's suggestion and then using

Duration: Format([TT]/86400,"hh:nn:ss")

but if it could be done in one that would be preferred.

 

[Gasman]

Hi everyone,

Sample DB attached.
On the weekend I managed to do it via two queries, but if it could be done in one, that would be appreciated.

The table is BT
The first query qryCallLogs
The second query qryTotalDuration