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?

edit retag close merge delete

Sort by » oldest newest most voted

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!

more

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?

more

Yes, this is the most easiest way. I build a lot of applications in ms-acces this way (engineer & sales activities)

( 2016-04-11 18:06:10 +0200 )edit
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.

more

@Ratslinger
The Question is tagged with base

basee != calc

( 2016-03-16 16:47:33 +0200 )edit