I have tried using the sum function for hours but it does not produce a correct answer and always undercounts. Why is this and what can I do to correct it?
You can sum a series using:
=SUM(HOUR(A1:A3))
What is needed for a correct result is to change the format of the cell where the calculation is. If you want HOURS only, the format Category is Time and on the Format Code line enter: [HH]
Edited for clarity. As noted, question was tagged as Base not Calc.
Thank You karolous my overlooked fault.
For Base, in the form properties, Data tab ‘Content type’ = SQL command. Then in the ‘Content’ field goes your SQL Sum statement such as : SELECT SUM("Id_Bike") FROM "Bike" You can generate this by using ellipsis (…).
Where “Id_Bike” is the field you want to sum & “Bike” is the table name where the field is located. Then in the field where you want this result, in the properties Data tab, select the ‘Data field’ (in this case): SUM("Bike"."Id_Bike") .
For totaling hours in a time field you want something like: SELECT SUM(HOUR("MyTime")) FROM "TimeCalc".
To add minutes to the calculation (partial dropped) :
SELECT SUM(("TotalMinutes" / 60) + "TotalHours") as "HoursTotaled" From (SELECT SUM(Minute ( "Time" )) as "TotalMinutes" , SUM(Hour ( "Time" )) as "TotalHours" FROM "TimeCalc")
Based upon your latest response (please use comment to reply), the following will give a total of Hours and Minutes in a format of HH:MM
`SELECT CONCAT("HRS","MIN") AS "TTLTIME" FROM (SELECT CONCAT(((SUM(Minute ( "Time" )) + SUM((Hour ( "Time" )) * 60)) / 60), ':') AS "HRS", MOD((SUM(Minute ( "Time" )) + SUM((Hour ( "Time" )) * 60)) ,60) AS "MIN" FROM "TimeCalc")`
To further clarify I’ve included a simple sample with a table of time elements and a form with the resulting total:TimeTotal.odb.
Thank you for your answer. I realise that if I just wanted whole hours I could define the field as numeric, but to add hours and minutes as HH:MM to get an answer in the same form as you say does not do it. I am thinking of trying 2 separate columns one for hours the other for minutes and define each as numeric and do the conversion in the SUM expression. Might that work?
Yes, this is the most easiest way. I build a lot of applications in ms-acces this way (engineer & sales activities)
ok I figured out a simple solution. This is my work that I am using. I was doing a time card. ColB3 is start time and ColC3 is end time. Format set for these columns is hh:mm:ss PM or the option that says 01:37:46 PM.
The in Col D total time (ColC-ColB)times 24 as in 24 hours, (C3-B3)*24. Then do a sum function as usual to add all the total hours up but you need to change the function cell format to Number General.
Works like a charm!
Sorry to dig out this old question but I studied carefully @Ratslinger answer and found a rounding error in the hours count.
I’d rather propose the following
SELECT CONCAT("HRS","MIN") AS "TTLTIME" FROM ( SELECT CONCAT(CAST(FLOOR(SUM(Minute ( "Time" )) / 60) AS "INT" ) + (SUM(Hour ( "Time" ))), ':') AS "HRS" , MOD((SUM(Minute ( "Time" )) + SUM((Hour ( "Time" )) * 60)) ,60) AS "MIN" FROM "TimeCalc" )
FLOOR() is avoiding the upper-rounding error and CAST() is required to get rid of an extra decimal that pops up any time the SUM()/60 is not a whole number
PS: (I was unable to open the example database TimeToal.odb on my French system but this is another story.)
Hope this helps, thankful regards for all the knowledge shared in this forum, Michel