Solved

Sum of times not resulting in time format...

Posted on 2006-10-27
10
403 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:makila
10 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 17822440
Wrap CDate() around your sum function:

Cdate(Sum(zzAdmin_Logouts.LogoutDurationTime))
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17822446
This will work so long as the sum never exceeds 23:59:59
0
 

Author Comment

by:makila
ID: 17822490
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
 
LVL 44

Accepted Solution

by:
GRayL earned 100 total points
ID: 17822645
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
 

Author Comment

by:makila
ID: 17822704
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 44

Expert Comment

by:GRayL
ID: 17823735
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17823949
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 17824011
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
 

Author Comment

by:makila
ID: 17835570
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
 
LVL 44

Expert Comment

by:GRayL
ID: 17836069
Thanks, glad I could help.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now