423,682 Members | 1,649 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,682 IT Pros & Developers. It's quick & easy.

Sum Time field

P: 20
I have a project that I need to be able to sum a short time field. does anyone know how I can do that?
I did try a query but it is not working

SQL:

Expand|Select|Wrap|Line Numbers
  1. SELECT (Sum(DatePart("h",[ActualLaborHours])*3600 + 
  2.             DatePart("n",[ActualLaborHours])*60)) AS TotalSeconds, 
  3.        Round([TotalSeconds]/3600,0) & ':' & 
  4.            Right("00" & Round(((TotalSeconds/3600) - 
  5.            Round(TotalSeconds/3600,0))*60,0),2) AS TotalTime
  6. FROM [Maximo Report]
  7. WHERE ((([Maximo Report].WorkType)="CM" 
  8.       Or ([Maximo Report].WorkType)="EM" 
  9.       Or ([Maximo Report].WorkType)="MMNRO" 
  10.       Or ([Maximo Report].WorkType)="MMROI" 
  11.       Or ([Maximo Report].WorkType)="PMCM" 
  12.       Or ([Maximo Report].WorkType)="PMINS" 
  13.       Or ([Maximo Report].WorkType)="PMOR" 
  14.       Or ([Maximo Report].WorkType)="PMPDM" 
  15.       Or ([Maximo Report].WorkType)="PMREG" 
  16.       Or ([Maximo Report].WorkType)="PMRT") 
  17.       AND (([Maximo Report].ActualStartDate) >= 
  18.           DateAdd("h",-1,[Enter the Start Date]) 
  19.       And ([Maximo Report].ActualStartDate) < 
  20.           DateAdd("h",23,[Enter the End Date])));

Attached Images
File Type: jpg 2018-10-30_14-29-28.jpg (58.9 KB, 155 views)
2 Weeks Ago #1

✓ answered by NeoPa

Summing a Short Time field is technically the same as Summing any Date/Time field. However, there are certainly issues.
  1. For it to make any sense then the values must represent deltas. That is to say periods of time as opposed to points in time.
  2. As SQL typically tries to protect you from doing anything as silly as the summing of points in time it won't allow the use of Sum() directly on Date/Time fields.
    To get past this though, you can use CDbl() around your Date/Time field. This works well as we know that Date/Times are essentially stored in special Double fields that are understood to contain Date/Time information.
  3. As the result of this Summing is now understood to be a general number rather than a Date/Time you will also need to convert back the result into Date/Time format.
    This can be an issue if the total is more than 31 days as the 'd' code in formatting is specifically the day of the month and not as simple as the number of days.
    If that is likely to be an issue we can follow up on that later. It's very unusual for time deltas to extend beyond that, though not impossible of course.
  4. Formatting should always be the last step. IE. You should always work with the unformatted data and only ever format it for the user to see.
  5. Fields in Queries have a Format attribute. Use this rather than the Format() function so that you don't lose the underlying data.
  6. I've found that the conversion function CDate() can also be used on the aggregated data to ensure the formatting is done for you automatically.
For the code itself you can use something like :
Expand|Select|Wrap|Line Numbers
  1. CDate(Sum(CDbl([ActualLaborHours]))) AS [TotalLaborHours]
The Format attribute should be something like "d hh:nn:ss".

Share this Question
Share on Google+
6 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,667
Jeannier1975,

You may have to explain your questions bit better. Summing time can be a bit challenging, and depends on what your starting values are and what you want the results to look like.

Adding dates “can” be as easy as 14-Aug-04 + 1 = 15-Aug-04, but it is clear from your code that you want to do something a little different than that. However, it also looks like your code is incredibly overcomplexified. I’ll take a look at it in greater detail a bit later.

What would be very helpful is an example or two of what your starting and resultant values would be if your code worked perfectly.

Thanks!
2 Weeks Ago #2

twinnyfo
Expert Mod 2.5K+
P: 2,667
If ActualLaborHours is a Short Time field, you should be able to simply Sum that field, and apply the Short Time format to the result to get what you want. For example, if you had two values: 8:35 and 4:05, you get a "sum" of 0.52777. But when you convert that into Short Time, you get 12:40.

Also, you can simplify the Where clause of your Query so that it is much shorter. You also need to aggregate your Query to properly show the totals. See below:

Expand|Select|Wrap|Line Numbers
  1. SELECT Sum([ActualLaborHours]) AS TotalTime
  2. FROM [Maximo Report]
  3. WHERE (([Maximo Report].WorkType 
  4.       IN ("CM", "EM", "MMNRO", "MMROI", "PMCM", 
  5.           "PMINS", "PMOR", "PMPDM", "PMREG", "PMRT")) 
  6.       AND ([Maximo Report].ActualStartDate >= 
  7.           DateAdd("h",-1,[Enter the Start Date]) 
  8.       And ([Maximo Report].ActualStartDate < 
  9.           DateAdd("h",23,[Enter the End Date]))
  10. GROUP BY Sum([ActualLaborHours]);
I think I got all the parentheses correct.
2 Weeks Ago #3

PhilOfWalton
Expert 100+
P: 1,373
Jeannie, What you want is comparatively simple, but first things first.

This is a horrible mess, and totally inflexible.
Expand|Select|Wrap|Line Numbers
  1. WHERE ((([Maximo Report].WorkType)="CM" 
  2.       Or ([Maximo Report].WorkType)="EM" 
  3.       Or ([Maximo Report].WorkType)="MMNRO" 
  4.       Or ([Maximo Report].WorkType)="MMROI" 
  5.       Or ([Maximo Report].WorkType)="PMCM" 
  6.       Or ([Maximo Report].WorkType)="PMINS" 
  7.       Or ([Maximo Report].WorkType)="PMOR" 
  8.       Or ([Maximo Report].WorkType)="PMPDM" 
  9.       Or ([Maximo Report].WorkType)="PMREG" 
  10.       Or ([Maximo Report].WorkType)="PMRT") 
What happens when you want to add another condition like
Expand|Select|Wrap|Line Numbers
  1.       Or ([Maximo Report].WorkType)="JENNIE")
Then you have to rewrite the Query.

So Create a table like this
Expand|Select|Wrap|Line Numbers
  1. TblWorksType
  2. WorkTypeID     WorkType      Selected
  3. 1                CM             Yes
  4. 2                EM             No
  5. 3                MMNRO          YES
  6. 4                abc            No
  7. 5                JEANNIE        Yes
  8.  
  9.  
The MaximoReport Table should be something like this

(NOTE the removal of the space between "Maximo" and "Report" because spaces in object names, field names and control names are a bad idea)
and the obvious relationship set up.

So here is the query
Expand|Select|Wrap|Line Numbers
  1. SELECT Sum(DatePart("h",[ActualLaborHours])*3600
  2. +DatePart("n",[ActualLaborHours])*600
  3. +DatePart("s",[ActualLaborHours])) AS TotalTime
  4. FROM MaximoReportJennie
  5. WHERE (((MaximoReportJennie.StartDate)>=#10/1/2018#) 
  6. AND ((MaximoReportJennie.EndDate)<=#11/12/2018# 
  7. And (MaximoReportJennie.EndDate) Is Not Null) 
  8. AND ((MaximoReportJennie.WorkTypeID) 
  9.  
  10. In (SELECT TblWorkTypeJeannie.WorkTypeID  
  11. FROM TblWorkTypeJeannie 
  12. WHERE (TblWorkTypeJeannie.Selected)=True)));
The first half of the query simply adds the LabourHours in seconds in the required date range.
The second part of the query restricts the query to only returning fields in the TblWorksType where you have selected the WorksType you want to include.

My final comment is that your method of inputting the Star and End Dates is far from elegant.
Many of my Databases have a form FrmDates with just 2 fields on it - StartDate and EndDate, and the queries refer to that form for date ranges

Phil
Attached Images
File Type: png MaximoReport.png (13.7 KB, 151 views)
2 Weeks Ago #4

NeoPa
Expert Mod 15k+
P: 31,084
Summing a Short Time field is technically the same as Summing any Date/Time field. However, there are certainly issues.
  1. For it to make any sense then the values must represent deltas. That is to say periods of time as opposed to points in time.
  2. As SQL typically tries to protect you from doing anything as silly as the summing of points in time it won't allow the use of Sum() directly on Date/Time fields.
    To get past this though, you can use CDbl() around your Date/Time field. This works well as we know that Date/Times are essentially stored in special Double fields that are understood to contain Date/Time information.
  3. As the result of this Summing is now understood to be a general number rather than a Date/Time you will also need to convert back the result into Date/Time format.
    This can be an issue if the total is more than 31 days as the 'd' code in formatting is specifically the day of the month and not as simple as the number of days.
    If that is likely to be an issue we can follow up on that later. It's very unusual for time deltas to extend beyond that, though not impossible of course.
  4. Formatting should always be the last step. IE. You should always work with the unformatted data and only ever format it for the user to see.
  5. Fields in Queries have a Format attribute. Use this rather than the Format() function so that you don't lose the underlying data.
  6. I've found that the conversion function CDate() can also be used on the aggregated data to ensure the formatting is done for you automatically.
For the code itself you can use something like :
Expand|Select|Wrap|Line Numbers
  1. CDate(Sum(CDbl([ActualLaborHours]))) AS [TotalLaborHours]
The Format attribute should be something like "d hh:nn:ss".
2 Weeks Ago #5

twinnyfo
Expert Mod 2.5K+
P: 2,667
Just as a point of clarification on the above, in MS Access it is, in fact, possible to Sum() a Date/Time field. In fact, I tested this prior to my post just to see what would happen. My assumption for the OP was that the “Short Time” meant “hours and minutes” and not “time of day”. So, if ActualLaborHours was 8:03, the intent of that field was “eight hour and three minutes worked” and not “began work at 8:03 am”.

When this is the case, as described above, using a Sum() on that field, will truly produce a sum of the total hours worked, which can then be translated back into a time format.

I just wanted to make sure others reading were aware of this possibility.
2 Weeks Ago #6

NeoPa
Expert Mod 15k+
P: 31,084
You're absolutely correct Twinny.

I just reviewed my own testing and it was only because the field I tried it on was a linked SQL Server DateTime2 field that it failed. Standard Access Date/Time fields don't need the extra dancing around.
2 Weeks Ago #7

Post your reply

Sign in to post your reply or Sign up for a free account.