Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing Time Fields (WXpHe+A2K)

    I have imported our time and billing logs into our Access db. From this we produce our monthly invoice to our clients, showing what we have done for them and how much we charge perfectly. From the main timelogs Tbl we have built a QryTimelogsStaff that only has 2 fields : Staff, with criteria [Select staff] and Elapsed time. This correctly displays all elaped time records for a member of staff. What I want to do is twofold: firstly to display the sum of the elaped times in another query that is based upon the QryTimelogsStaff - so far I am unable to sum(elapsedtime) get a correct answer in this, the correct answer for my newest member of staff is 57:00:00, but in spite of formatting elapsedtime to Longtime format in both Tbl and Qry I get 03:59!!

    Secondly I would like to display the sum(elapsedtime) for a selected staff member with a monthly total.

    (Hair tearing out for me - what is left of it) Assistance will be very much appreciated... ta!
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Summing Time Fields (WXpHe+A2K)

    What you may have to do is convert the time into seconds and sum that, then convert the summed seconds back to HH:MM:SS.
    Pat <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Summing Time Fields (WXpHe+A2K)

    Is your Elapsed Time field a data/time field? I don't see how you could be getting 59:00:00 (59 hours) if that was the case.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing Time Fields (WXpHe+A2K)

    Hi Mark, please see the attached snapshot of the qry in question pic 1 shows the data to be summed and pic 2 the result, which is where the problem lies!
    Attached Images Attached Images
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing Time Fields (WXpHe+A2K)

    pic 2
    Attached Images Attached Images
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Summing Time Fields (WXpHe+A2K)

    Hi Stephen,
    This may not help you, but you should know that Time fields are the same as Date fields, and they store the result in a decimal structure where the integer portion represents the number of days since 12/31/1899, and the decimal portion represents the time of day. So in order to get a sum of time fields, you have to take the number of days and multiply it by 24 when you get a sum. You should be able for format your result as a Date/Time result and make some sense of it that way, but you may need to use the Hour() and Min() functions as well as Int() in order to get a correct result.
    Wendell

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Summing Time Fields (WXpHe+A2K)

    You never did respond to my question as to whether or not your Elapsed Time is a date/time field. The reason is that if you sum these fields and try the display the result with the hh:nn:ss format, you only get the time component up to 24 hours! It converts everything above that into days. If you want to display an elapsed time of 57 hours as 57:00:00, then you will have to write a special function to return this value.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing Time Fields (WXpHe+A2K)

    The data is imported into Long time fields, so what happens next please?
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summing Time Fields (WXpHe+A2K)

    Access has just one type of field for dates *and* times: the Date/Time field. As mentioned before in this thread, the integer part of a Date/Time field represents the date or a number of days (where 0 corresponds to December 31, 1899) and the fractional part of a Date/Time field represents time (.0 = midnight, .5 = noon). The Format property determines how the value in a date field is displayed - just the date part, or just the time part, or both. There is no such thing as a Long Time field; you probably have a Date/Time field *formatted* as Long Time.

    Unlike Excel, Access has no way of formatting time to display accumulated times with a number of hours above 23. You can simulate it by creating another text box.
    Set the Control Source property of this text box to
    =Int(24*[SumOfElapsed Time]) & Format([SumOfElapsed Time]-Int(24*[SumOfElapsed Time])/24,":nn:ss")
    The first part of this expression computer the whole number of hours; the second part computes the remainder and formats it as minutes and seconds.

    Note: this text box contains a string value; it can't be used for futher calculations. Use the original value [SumOfElapsed Time] for calculations.

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Summing Time Fields (WXpHe+A2K)

    There is no such thing as "Long time fields" in Access.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    BECCLES, Suffolk, England
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing Time Fields (WXpHe+A2K)

    Thank you Mark for pointing out my error. Of course you are right - there are no "Long Time fields" in Access. Just, in my case, "Date/Time" firlds formatted to Long Time.
    <IMG SRC=http://www.wopr.com/w3tuserpics/StephenElms_sig.jpg> Didn't think that I made my first post here on 5th February 2001...!

  12. #12
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Summing Time Fields (WXpHe+A2K)

    Your problem is that you can't really sum Times and then try to use the standard time format of hh:nn:ss, etc. A date field keeps track of time as a decimal amount of 1 day, so any amount more than 24 hours is a day. You will have to create a custom function to display the summed time yourself.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  13. #13
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Summing Time Fields (WXpHe+A2K)

    Not sure how your ElapsedTime data is being calculated before it is imported, or what actual data is stored in this field, so not sure if this will help. These examples use a table named tblTime with 3 fields, Staff (text), and StartTime & EndTime (Date/Time, General Date format). Elapsed Time is calculated based on the 2 date/time fields; it is not saved in table. Example of query displaying individual records:

    SELECT Staff, StartTime, EndTime, GetElapsedTime([StartTime],[EndTime]) AS [Elapsed Time]
    FROM tblTime
    ORDER BY Staff, StartTime;

    This uses GetElapsedTime function which returns a string representing Elapsed Time in hours, minutes, and seconds:

    Public Function GetElapsedTime(dblStartTime As Double, _
    dblEndTime As Double) As String

    Dim lngElapsedTime As Long
    Dim intHour As Integer
    Dim intMin As Integer
    Dim intSec As Integer

    lngElapsedTime = DateDiff("s", dblStartTime, dblEndTime)
    intHour = lngElapsedTime 3600 'Integer division operator =
    intMin = (lngElapsedTime Mod 3600) 60 'Divide remainder by 60
    intSec = (lngElapsedTime Mod 3600) Mod 60 'Remainder

    GetElapsedTime = intHour & ":" & _
    Format(intMin, "00") & ":" & _
    Format(intSec, "00")

    End Function

    Example of query that sums Elapsed Time by Staff member using this function:

    SELECT Staff, GetElapsedTime(Sum([StartTime]),Sum([EndTime]))
    AS [Time Elapsed]
    FROM tblTime
    GROUP BY Staff
    ORDER BY Staff;

    You may be able to adapt some of this for what you are doing. Keep in mind the function returns text strings for display purposes only; if doing further calculations you need to use actual elapsed time values.

    HTH

Posting Permissions

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