Sum Short Time Field

[jeannier1975]

Good Day,
I have a query where i need to total the Labor hours
I have a field label ActualLabor hours and i want to take the total of that field of all the workorders and sum it.How would I go about it.


I have started the SQL Query
where for the time field TotalSeconds: (Sum(DatePart("h",[ActualLaborHours])*3600+DatePart("n",[ActualLaborHours])*60))
and the other field:
TotalTime: Round([TotalSeconds]/3600,0) & ':' & Right("00" & Round(((TotalSeconds/3600)-Round(TotalSeconds/3600,0))*60,0),2)


when i run the query i get an error stating [FONT=&quot]This expression is typed incorectly or is too complexed to be evualated does anyone know how i can do a query that adds hours.

[/FONT][/COLOR]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 TotalTimeFROM [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])));


[COLOR=#333333][FONT=&quot]/CODE][/FONT][/COLOR]

 

[Micron]

Suggest you break it into manageable chunks and move on when you successfully perform a calculation. While doing so, eliminate Sum functions if you're simply going to add 2 values such as in TotalSeconds:DatePart("h",[ActualLaborHours])*3600 + DatePart("n",[ActualLaborHours])*60. Also, you are trying to add text ( : ) to numbers but you cannot. You would at least have to use Format function on the number parts, or perhaps better, a type conversion function such as CStr to convert numbers to text and concatenate (not add) the parts together. Also, I dont get this: TotalSeconds/3600)-Round(TotalSeconds/3600,0) Looks to me like you're dividing a number by 3600 and subtracting from that the same number divided by 3600, both of which ought to be rounded to zero decimal places since you specify 0 in one but default to 0 in the other. I don't see how that can be anything but zero, in which case, the outer round to 2 decimal places should amount to zero.

It really isn't clear what you're trying to do anyway. ActualLaborHours sounds like a number, yet you're using a date function on it. What may not be apparent to you is that date/time is stored as a year/month/day/hour/minute/second value. If the time portion isn't specified, it defaults to midnight, but regardless, what's underlying that is still a double precision floating point number. What makes it appear as a date is the format. So to get the hour portion of a date is to get, for example, 9 for 9 o'clock. Not sure how that translates into labor hours. So even if you fix the complexity of the expressions and get them to return a result, I can't imagine it will be useful.

Then there is the notion that perhaps you are trying to get the sum of hours worked over a set of records, in which case your calculations should be performed vertically (over one field in a set of records) and not horizontally. To do that would require domain functions (e.g. DSum) or first building a useful Totals query. Again, doing so over date values and calling those numbers 'hours worked' doesn't make sense to me.

 

[xenou]

What is the data type of actual labor hours? Do you have sample data? Normally that should be a simple as Sum([ActualLaborHours]).