How to get a Proper total time value?

Question

Using Access 2003

Table

PersonID TotalHours

111    12.23.34
111    23.32.23
111    14.32.23
222    00:23:32
222    01:00:00

So on…,

TotalHours Datatype is Datetime

Query Used.

Select Personid, sum (TotalHours) from table group by personid.

It Showing

Personid TotalHours

111 4.30842592592593
222 7.93241898148148

so on...,

I want to show a proper time format in Sum (TotalHours). Like (HH:MM:SS)

Expected Output

Personid TotalHours

111 32:44:23
222 23:11:22

So on...,

Need Query Help.

Answer 1

What's happening is that it's totalling the numeric representation of the times

You can do this:

Select Personid, cdate(sum (TotalHours)) as GrandTotal
from table 
group by personid.

But you are going to get results that look like:

111   1900-01-01 2:28:10 AM
222              1:23:32 AM

This because Personalid 111 exceeds 24 hours

What you really have to do is smoosh the times down to seconds; then add them; and then break it out into hours/minutes/seconds again; I did this with two queries

[time_1 definition]

SELECT time_tab.pid,
    Sum(Hour([time]) * 3600
    + Minute([time]) * 60
    + Second([time])) AS time_total
FROM time_tab
GROUP BY time_tab.pid;


[time_2 definition]

SELECT time_1.pid, 
    Int([time_total]/3600) AS h,  
    Int(([time_total]-Int([time_total]/3600) * 3600)/60) AS m, 
    [time_total]
        - (Int(([time_total]-Int([time_total]/3600) * 3600)/60)*60 
            + (Int([time_total]/3600)*3600)) AS s
FROM time_1

which gives me

111   50  28  10
222   1   23  32

OR

[time_2 definition]

SELECT time_1.pid, 
    Int([time_total]/3600) & ":" 
        & Int(([time_total]-Int([time_total]/3600) * 3600)/60)  & ":" 
        & [time_total] 
            - (Int(([time_total]-Int([time_total]/3600) * 3600)/60) * 60
            + (Int([time_total]/3600)*3600)) AS grand_total
FROM time_1

which gives me

111   50:28:10
222    1:23:32