Results 1 to 5 of 5
  1. #1
    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. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use:
    Code:
    SELECT Time_In, Time_Out, <Other Columns...>, DateDiff("h", Time_In, Time_Out) AS Elapsed_Time FROM <Table>...
    Have a nice day!

  3. #3
    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. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

  5. #5
    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
  •