Total hours in Report over 24 hours

[jimmyc50]

I have a 2016 access database that my maintenance team enters daily activities. I have a field called manhours. I created a report to show manhours entered for a period of time. The grand total ( SUM ) is greater than 24 hours. The total manhours should be 62:10 but is displaying in the report as 14:10. The manhours entered are in short time format. I am very new to access and need help.

[Narender Sagar]

Just take out the difference of two dates and multiply it with 24 in a query. e.g. if you have fromDate and ToDate fields, then in query design view, create a calculated field, as follows :

ManHrs:([ToDate]-[FromDate]*24)

[jimmyc50]

Not sure of your answer. Lets say I have 5 people. they each enter 8 hours for today. The report is showing each members total hours entered today at 8 hours. Then at the bottom it shows a grand total of 16:00. But this should be 40:00. I have tried all combinations in the format field but can not get to display totals over 24 hours.

[Narender Sagar]

You need to elaborate more about- what are the field you have for maintaining the date/time.If you can attach sample database, it will be easy to understand.

[Carl Witte]

Hi Jimmy. I'll throw out some guesses... Could you be successfully showing hours, but that dates are being missed. (In your example, you have 8*5 or 40 and you are showing 16 which is the remainder of hours after the date is in the ones place.) If this is the case than Narender's solution will work correctly for you if you are using code SQL.

If this isn't the case, I recommend, if you cannot upload a copy as Narender said, you look and verify what type of information the field is designed to hold (table design) and what code if any is being run.

A final guess, to me it sounds like you are using a report to aggregate your data and the report is 'helping.' If I have correctly added one and one and made 3, then the solution may be as simple as changing the format of the field in the report.

[PhilOfWalton]

This bit of a query extracts the hours & minutes from a table of dates, adds then up and displays the total.

SELECT Sum(Hour([PaymentDate])*60 + Minute([PaymentDate])) AS TotTime, 
(Sum(Hour([PaymentDate])*60 + Minute([PaymentdDate]))\60)*60 AS Hrs, 
[TotTime]-[Hrs] AS Mins, 
[Hrs] & ":" & [Mins] AS Result
FROM Payments;
 

If you include something similar in your query, I hope you get the result you require

Phil

[jforbes]

What Datatype are you using for your Hours?