Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sum of times not resulting in time format...

Posted on 2006-10-27
10
Medium Priority
?
410 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

660 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