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]

Hi, in the query designer you need to set the notation property of the field where you sum the time field to hh:mm

 

[Jlloyd]

Hi
Thanks for that but I have tried to set the property on the field I sum (Amount of Time) to hh:nn. I was of the understanding that access uses n instead of m for minutes because m is for month. It automatically shows in the property as \hh:nn\. This is the same as short time so will only sum up to 24 hours. I will need it to sum to over 24 hours if required. It's beginning to seem impossible yet so easy in Excel.

 

[Kreszch68]

Ahh, ok I stopped reading after the decimal issue. The problem is that you use a date field to store elapsed time. A date field is a point in time, not an ‘amount’ of time, but there is a work around for this.</SPAN></SPAN>
Using the Datepart function you can extract hours, minutes and seconds from the date field. From there you can normalize the parts to seconds, which then are summed to the total of seconds spend on a job. Of course you don’t want to report in seconds, so from the total in seconds you’ll need to break down in hours and minutes and format the outcome to the hh:mm notation.</SPAN></SPAN>
To make it a little less complicated, I’ll give you an example in SQL.</SPAN></SPAN>
Assuming we have a table called tJobLog with a field JobNumber and a TimeSpend field.</SPAN></SPAN>

First we need to make a calculation for the total of seconds from the field TimeSpend.
</SPAN></SPAN>

DatePart("h",[Timespend])*3600: calculates the hours from TimeSpend to seconds</SPAN></SPAN>
DatePart("n",[TimeSpend])*60: calculates the minutes from TimeSpend to seconds</SPAN></SPAN>

Add up these two numbers for the total of seconds:</SPAN>
</SPAN>

DatePart("h",[Timespend])*3600+DatePart("n",[TimeSpend])*60)

And add the Sum operator as we need it to sum over the group:</SPAN>
</SPAN>

(Sum(DatePart("h",[Timespend])*3600+DatePart("n",[TimeSpend])*60)) AS TotalSeconds</SPAN></SPAN>

Now that we have the TotalSeconds, we need to break down the hours and minutes from the summation.</SPAN></SPAN>
The total hours are the TotalSeconds devided by 3600 and rounded to 0 decimals, resulting in:
</SPAN></SPAN>

Round([TotalSeconds]/3600,0)</SPAN></SPAN>

The minutes, are the residue of the TotalSeconds devided by 3600 minus the total hours, multiplied by 60, rounded to 2 digits, resulting in:</SPAN>
</SPAN>

Round( ((TotalSeconds/3600) - Round(TotalSeconds/3600,0))*60,0),2)</SPAN></SPAN>

Now the last thing is to put the hours and minutes together and format this as hh:mm:</SPAN></SPAN>

Round([TotalSeconds]/3600,0) & ':' &  Right("00" &  Round( ((TotalSeconds/3600) - Round(TotalSeconds/3600,0))*60,0),2)  AS TotalTime</SPAN></SPAN>

The final SQL looks like:
</SPAN></SPAN>

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>

 

[Jlloyd]

Just got back from holiday and thought I'd have a go. I have not used SQL before. What impact can changing the databse to SQL Server have? will it affect any other queries etc.? Could you tell me how to go about it please. Thanks

 

[Jlloyd]

Just realised I can o this in SQl view. Will give it a go and let you know how I get on. Thanks for your help.

 

[Jlloyd]

This works! Thanks so much. Just one question though. When it shows the answer it's showing the total amount of seconds in the view. I have tried in Design View to untick the box so that it doesn't show. When I do this it then becomes a parameter query and the box appears asking for total seconds.
How can I stop the total amount of seconds from showing in the answer without this happening?

 

[Kreszch68]

Save the query and use the query to create a new select query to only display the fields you want.

 

[Jlloyd]

Will do. Thank you for all your help. Really appreciated.

 

[Jlloyd]

I have created another query and only used the fileds I require but it is still showing as a parameter query when I run it. It asks for the total seconds in the message box. I haven't even used this field in the new query. Any help appreciated.