Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sum of times not resulting in time format...

Posted on 2006-10-27
10
Medium Priority
?
415 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 44

Accepted Solution

by:
GRayL earned 400 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
 
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

578 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