Solved

Sum of times not resulting in time format...

Posted on 2006-10-27
10
408 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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 …
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

615 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