# Thread: How to add total times in a query?

1. Registered User
Join Date
Aug 2004
Posts
364

## Unanswered: How to add total times in a query?

Hello,

I have a database which logs peoples visits to centres (time in & time out). It adds up each visits total time (time in + time out = hours)

I want to create a query which adds up the total amount of time each person has spent. So if one person comes in to a centre ten times for two hours each the total in the query should be 20 hours.

I need a query to do this for every record!

2. Moderator
Join Date
Mar 2009
Posts
5,442
You could use:
Code:
`SELECT Time_In, Time_Out, <Other Columns...>, DateDiff("h", Time_In, Time_Out) AS Elapsed_Time FROM <Table>...`

3. Registered User
Join Date
Aug 2004
Posts
364
Thankyou for taking time to reply!

There are a few problems, first the elapsed time only shows up in hours, but I need hours and minutes.

Secondly If someone has been in to a centre on ten occasions I dont need to see their name ten times, I need to see them once in the query with the total time they have spent from all their visits to a centre.

4. Moderator
Join Date
Mar 2009
Posts
5,442
You wrote "(time in + time out = hours)", that's why I gave you the formula in hours. If you want to have it in minutes too, you'll have to combine two DateDiff() function (one with "h" and one with "n"):
Code:
`..., DateDiff("h", Time_In, Time_Out) & ":" & DateDiff("n", Time_In, Time_Out) AS Elapsed_Time FROM <Table>...`
or compute the difference between both times in minutes and convert it to hours:minutes. In such a case I would write a VBA function and call it from the query:
Code:
```Function ElapsedTime(ByVal Time1 As Variant, ByVal Time2 As Variant) As Variant

Dim lngMinutes As Long

If Not IsNull(Time1) And Not IsNull(Time2) Then
lngMinutes = DateDiff("n", Time1, Time2)
ElapsedTime = CStr(lngMinutes \ 60) & ":" & CStr(lngMinutes Mod 60)
Else
ElapsedTime = Null
End If

End Function```
Then:
Code:
`SELECT Time_In, Time_Out, <Other Columns...>, ElapsedTime(Time_In, Time_Out) AS Elapsed_Time FROM <Table>...`
You can also compute the elapsed time in decimal if you prefer; just change the line ElapsedTime = CStr(... in the function.

If you want to group the result by person, you'll have to use a more complex query including a GROUP BY clause in your SQL Statement.

5. Registered User
Join Date
Aug 2004
Posts
364
Thankyou!!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•