Sum of Time in Access Query

[Jlloyd]

Hi
I have a table (Time Log) that records the amount of time a person spends doing a job [Amount of Time]. They log the time in the format hh:mm. This works fine.
I want to do a query to add up the amount of time is spent on a particular Job (they log the time against a job number).
I have used the Totals command to Sum the Amount of Time but the answer is the time as its decimal. I know about time in Excel and to format time when the anwer could be more than 24 hours I format it to [hh]:mm.
I have tried lots of different formats through properties in the query using hh:nn and varying formats of this but cannot get the correct answer.
I am sure there must be a simple format for this like in Excel without have to change everything to minutes then back to hours and minutes etc.
Please help! Very much appreciated.

 

[Kreszch68]

It should work if you use two queries, one to do the math and one to select the fields you need.

So first create a query with the next SQL:

SELECT (Sum(DatePart("h",[Timespend])*3600+DatePart("n",[TimeSpend])*60)) AS TotalSeconds, Round([TotalSeconds]/3600,0) & ':' &  Right("00" &  Round(((TotalSeconds/3600) - Round(TotalSeconds/3600,0))*60,0),2)  AS TotalTime </SPAN></SPAN>FROM tJobLog </SPAN></SPAN>GROUP BY tJobLog.JobNumber</SPAN></SPAN>
</PRE>

And call it let's say, MathQuery

Then you create an other query like:

Select TotalTime From MathQuery

 

[Jlloyd]

I have the total time now showing in my query, thank you. Now I need the Job number, by which it is grouped, to show in the query. I have tried to do this but for some reason cannot get this to show. I will also need to have a parameter query the same as the above, which allows the user to type in the job number they want the total time for. I will need this and to be able to split it by employee so that the user types in the job number, sees the time spent on that job split by employees. Thank you again for all of your help.

 

[Triplehelix]

Super old thread, but I worked my way through this same situation but using a calculated field rather than SQL code, just felt easier at the time:

Duration: (DateDiff('s',[StartTime],[EndTime])\3600 & Format((DateDiff('s',[StartTime],[EndTime])\60)-((DateDiff('s',[StartTime],[EndTime])\3600)*60),'\:00') & Format(DateDiff('s',[StartTime],[EndTime]) Mod 60,'\:00'))

Produces a hh:mm:ss formatted time where the hh range is a count of total hours and is not constrained to a 24 hour period of time (ie start=1/1/2016 00:00:00 end=1/5/2016 08:15:15, calculation will show 104:15:15