Sum Time field

[jeannier1975]

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:

SELECT (Sum(DatePart("h",[ActualLaborHours])*3600 + 
            DatePart("n",[ActualLaborHours])*60)) AS TotalSeconds, 
       Round([TotalSeconds]/3600,0) & ':' & 
           Right("00" & Round(((TotalSeconds/3600) - 
           Round(TotalSeconds/3600,0))*60,0),2) AS TotalTime
FROM [Maximo Report]
WHERE ((([Maximo Report].WorkType)="CM" 
      Or ([Maximo Report].WorkType)="EM" 
      Or ([Maximo Report].WorkType)="MMNRO" 
      Or ([Maximo Report].WorkType)="MMROI" 
      Or ([Maximo Report].WorkType)="PMCM" 
      Or ([Maximo Report].WorkType)="PMINS" 
      Or ([Maximo Report].WorkType)="PMOR" 
      Or ([Maximo Report].WorkType)="PMPDM" 
      Or ([Maximo Report].WorkType)="PMREG" 
      Or ([Maximo Report].WorkType)="PMRT") 
      AND (([Maximo Report].ActualStartDate) >= 
          DateAdd("h",-1,[Enter the Start Date]) 
      And ([Maximo Report].ActualStartDate) < 
          DateAdd("h",23,[Enter the End Date])));

[twinnyfo]

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!

[twinnyfo]

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:

SELECT Sum([ActualLaborHours]) AS TotalTime
FROM [Maximo Report]
WHERE (([Maximo Report].WorkType 
      IN ("CM", "EM", "MMNRO", "MMROI", "PMCM", 
          "PMINS", "PMOR", "PMPDM", "PMREG", "PMRT")) 
      AND ([Maximo Report].ActualStartDate >= 
          DateAdd("h",-1,[Enter the Start Date]) 
      And ([Maximo Report].ActualStartDate < 
          DateAdd("h",23,[Enter the End Date]))
GROUP BY Sum([ActualLaborHours]);

I think I got all the parentheses correct.

[PhilOfWalton]

Jeannie, What you want is comparatively simple, but first things first.

This is a horrible mess, and totally inflexible.

WHERE ((([Maximo Report].WorkType)="CM" 
      Or ([Maximo Report].WorkType)="EM" 
      Or ([Maximo Report].WorkType)="MMNRO" 
      Or ([Maximo Report].WorkType)="MMROI" 
      Or ([Maximo Report].WorkType)="PMCM" 
      Or ([Maximo Report].WorkType)="PMINS" 
      Or ([Maximo Report].WorkType)="PMOR" 
      Or ([Maximo Report].WorkType)="PMPDM" 
      Or ([Maximo Report].WorkType)="PMREG" 
      Or ([Maximo Report].WorkType)="PMRT") 

What happens when you want to add another condition like

      Or ([Maximo Report].WorkType)="JENNIE")

Then you have to rewrite the Query.

So Create a table like this

TblWorksType
WorkTypeID     WorkType      Selected
1                CM             Yes
2                EM             No
3                MMNRO          YES
4                abc            No
5                JEANNIE        Yes
 
 

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

SELECT Sum(DatePart("h",[ActualLaborHours])*3600
+DatePart("n",[ActualLaborHours])*600
+DatePart("s",[ActualLaborHours])) AS TotalTime
FROM MaximoReportJennie
WHERE (((MaximoReportJennie.StartDate)>=#10/1/2018#) 
AND ((MaximoReportJennie.EndDate)<=#11/12/2018# 
And (MaximoReportJennie.EndDate) Is Not Null) 
AND ((MaximoReportJennie.WorkTypeID) 
 
In (SELECT TblWorkTypeJeannie.WorkTypeID  
FROM TblWorkTypeJeannie 
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

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

CDate(Sum(CDbl([ActualLaborHours]))) AS [TotalLaborHours]

The Format attribute should be something like "d hh:nn:ss".

[twinnyfo]

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.

[NeoPa]

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.