Sum Time Field in Access

I have three tables created in MS access to track Cycling/gear usage, First table has Activity ID and cycling duration in time (hh:mm:ss), second table has Activity ID and Gear ID, third table has gear ID and Name of the gear, all three tables are connected using relationship.

Im looking to track total gear usage in hours, for example : Gear XX : 135h:45mm:59ss, but access is looking duration field as Date and Time (24 hours format) and the sum result looks like a time format (24 Hours). Duration field is created as Date/Time in table, is it possible to sum time in access? have tried changing the format to hh:nn:ss, i does not seems to work. All tables are created using Excel sheet import and i dont have start or end time to use something like datediff.

How Date/Time data type works

The Date/Time data type can store both dates and times. For time-only values, you can store just the time component (e.g., 09:30:20 or 8:45:20).

Time is store as fraction of a day:

Total Day = 24 hours X 60 Minutes X 60 Seconds = 86400 Seconds

if Time is 6:00:00 then It will be (6 X 60 X 60)/86400 = .25

To get the result as mentioned in the question there will be a function which will convert this decimal value into hours, minutes and seconds.

The Tables will be

• tblActivity (ActivityID Number, Duration Date/Time)
• tblCyclingByGear (ActivityID Number, GearID Number)
• tblGear (GearID Number, GearName ShortText)

Data in tblDuration

Data in tblCyclingByGear

Data in tblGear

Function to calculate hour, minute, second from summation of Date/Time field

Public Function TotTime(TValue As Double) As String
    Dim TotalMinutes As Long
    Dim Hours As Long
    Dim Minutes As Long
    Dim Seconds As Long
    Dim TimeValue As Date
    Dim bHour As Long
    bHour = 0
    If TValue >= 1 Then
        bHour = Int(TValue)
        bHour = bHour * 24
    End If
   ' Initialize total minutes
    TotalMinutes = 0
    
    TimeValue = TValue
    
    TotalMinutes = TotalMinutes + (Hour(TimeValue) * 60) + Minute(TimeValue) + (Second(TimeValue) / 60)
    
    ' Calculate hours, minutes, and seconds
    Hours = TotalMinutes \ 60
    Minutes = TotalMinutes Mod 60
    Seconds = (TotalMinutes * 60) Mod 60
    Hours = bHour + Hours
    ' Format the result as hh:mm:ss
    TotTime = Format(Hours, "00") & "h:" & Format(Minutes, "00") & "mm:" & Format(Seconds, "00") & "ss"
End Function

Use of this function(TotTime) in MS Access Query

SELECT b.GearID, c.GearName, TotTime(Sum(a.Duration)) AS TotalTime, Sum(a.Duration) AS RawTime
FROM tblActivity AS a, tblCyclingByGear AS b, tblGear AS c
WHERE a.ActivityID = b.ActivityID and b.GearID = c.GearID
GROUP BY b.GearID, c.GearName;

The result of the query

Implemented query and functions in MS Access 2016