Sum of times not resulting in time format...

Hi,

I am building a query to add up all the time a user is logged out for a certain day. Each user has multiple entries for single day. The LogoutDurationTime in the Admin_Logouts table is formatted as "short time".

Here's what the query looks like in SQL view:

SELECT zzAdmin_Logouts.LoginDate, zzAdmin_Logouts.AgentName, zzAdmin_Logouts.CMSLoginID, zzAdmin_Logouts.CUID, Sum(zzAdmin_Logouts.LogoutDurationTime) AS SumOfLogoutDurationTime, zzAdmin_Logouts.Supervisor
FROM zzAdmin_Logouts
GROUP BY zzAdmin_Logouts.LoginDate, zzAdmin_Logouts.AgentName, zzAdmin_Logouts.CMSLoginID, zzAdmin_Logouts.CUID, zzAdmin_Logouts.Supervisor
ORDER BY zzAdmin_Logouts.AgentName;

Here is the result:

LoginDate      AgentName      CMSLoginID      CUID      SumOfLogoutDurationTime      Supervisor
10/2/2006      User 1                 2651                     us4264      2.08333333333333E-03      Supervisor 1
10/5/2006      User 1                 2651                     us4264      2.08333333333333E-03      Supervisor 1
10/6/2006      User 1                 2651                     us4264      6.94444444444444E-03      Supervisor 1

The result I want is:

LoginDate      AgentName      CMSLoginID      CUID      SumOfLogoutDurationTime      Supervisor
10/2/2006      User 1                 2651                     us4264      01:30:05                         Supervisor 1
10/5/2006      User 1                 2651                     us4264      01:01:59                       Supervisor 1
10/6/2006      User 1                 2651                     us4264      01:22:03                       Supervisor 1

I am not sure why the sum looks like that. I have another query with the same datatypes that does the same thing but the results are in the time format not numerical format.

Anybody know how to fix this or what could be wrong?

Thanks in advance!
Makila
makilaAsked:
Who is Participating?
 
GRayLConnect With a Mentor Commented:
You can change your system setting to a 24 hour clock, (like all 'normal' people) or wrap the sum function in a format function:

Format(Sum(zzAdmin_Logouts.LogoutDurationTime)),"hh:nn:ss")

I cannot see how the decimal values from your query willl give you your expected durations.  As the decimal part of a datetime field (actually a double precision number in Access) is the decimal part of a day, any value E-3 cannot yield a value greater than about 15 minutes, yet your logoutduration times all show values greater than one hour.
0
 
GRayLCommented:
Wrap CDate() around your sum function:

Cdate(Sum(zzAdmin_Logouts.LogoutDurationTime))
0
 
GRayLCommented:
This will work so long as the sum never exceeds 23:59:59
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
makilaAuthor Commented:
That kind of got the results I'm looking for.

Here's what that resulted in:

LoginDate      AgentName      CMSLoginID      CUID          SumOfLogoutDurationTime      Supervisor
10/2/2006      User 1                 2651                      am4264      12:03:00 AM                           Supervisor 1
10/5/2006      User 1                 2651                      am4264      12:03:00 AM                           Supervisor 1
10/6/2006      User 1                 2651                      am4264      12:10:00 AM                           Supervisor 1

I need the SumOfLogoutDurationTime to look like this 01:30:05 (1 hour 30 minutes 5 seconds). Do I have to wrap another function around the CDate function?
0
 
makilaAuthor Commented:
That's the thing. I don't know why the duration times show up at decimals in the first place. The table itself has durations times in the time format 01:30:59 but when I sum them in the query, it turns them into the decimals...
0
 
GRayLCommented:
While the format of a number can be expressed in a datetime manner, it is nontheless a number.  When you add numbers you get a number.  If you want that number back in a datetime format you have to say so by wrapping with CDate() or Format().  With a 12 hour clock, times will always have an AM or PM trailer with CDate() - not what you want to see.  With a 24 hour clock, you are good to 23:59:59 as I said before.  With Format(myTime,"hh:nn:ss") you will get a 24 hour clock representation but it will be a string value.  Where are we at?
0
 
jefftwilleyCommented:
Show us what you get if you just pull straight data without summing it in your example query. After you try G's suggestion of course. I Agree with him by the way....24 hour clock.
J
0
 
Arthur_WoodCommented:
YOu need to understand that Access stores Date/Time values as Numbers, with a Decimal point.  The Integer part of the Number is the Number of days since the base date of Dec 30, 1899.  The fractional part of the Date/Time value is the Time, measured in seconds since Midnight, as a fraction of 1 day (1 day = 24 hours = 86400 seconds)

This right now it is 10/27/2006 9:04:51 PM EDT which translates to 39017.8782407407

that is, Today is 39017 days since Dec 30, 1899 and the time is 0.8782407407 of 1 day since Midnight

That is why your intervals are showing up as Decimal fractions.

AW
0
 
makilaAuthor Commented:
Using CDate and changing my system time to 24 hour clock gave me the results in the format I was looking for. Thanks!

Running the query w/o summing it looks like this (hh:ss) :

AgentName CUID      CMSLoginID LoginDate  LogoutDurationTime      Supervisor
User 1       am4264      2651        10/2/2006  00:03                      Supervisor 1
User 1       am4264      2651        10/5/2006  00:03                      Supervisor 1
User 1       am4264      2651        10/6/2006  00:01                      Supervisor 1
0
 
GRayLCommented:
Thanks, glad I could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.