Calculating time

[dufc]

Hi there folks, newbie to the forum and newbie to Access.

I have problem that I hope you guys can help me with.

I have a table which I have inmported from an xls spreadsheet. It shows me a StartTime, a FinishTime and a TotalTime worked.

I want a report that will show me these records and give me a total time of all the TotalTime totals if that makes sense.

I have stuck a text box on my report and used =sum([TotalTime]). This works fine, but my problem is that as soon as it totals more that 24 hours, I think it goes back to 0 and starts counting again so my total is wrong.

Is there anything I can do about this?

 

[maxmangion]

Hi dufc and welcome to the forum.

As regards the calculate time, search this forum for "calculate time" as there are several examples how to deal with sum which exceeds the 24 hours.

Moreover, you shouldn't be storing the totaltime in your table, but that should be done at query level as it is not recommended to have calculated fields in a table.

 

[dufc]

Thanks for the reply maxmangion, appreciate it. I understand I shouldnt hold a calculated value at table level and should calculate it in the query. Like I say, I have just pulled this data that was already in a spreadsheet. I am just playing around with it at the mo so can change that in the future.

Thing is, even if I calculate the time in the query, I will still have the same problem with totalling all the TotalTimes together.

 

[maxmangion]

Thing is, even if I calculate the time in the query, I will still have the same problem with totalling all the TotalTimes together.

I agree with you, but it was a suggestion so you don't build new objects (forms/reports) based on that table setup, because you will have a lot of maintenance to do in the future.

As i said in my original post if you try searching the forum for calculated time you will certainly solve your problem, as this topic is a common one and has been discussed several times.

 

[boblarson]

Thing is, even if I calculate the time in the query, I will still have the same problem with totalling all the TotalTimes together.

Not true. You can do a separate aggregate query to total the times together.

 

[dufc]

Not true. You can do a separate aggregate query to total the times together.

Can you point me in the right direction of how I would achieve this? Thanks.

 

[dufc]

OK I have now built a query that calculates the TotalTime using the StartTime and FinishTime fields.

I am now trying to build a report which uses the query as its record source.

Like I said earlier, I now need a grand total of all those TotalHours on the report. I did a search on this forum and found this:-

http://www.access-programmers.co.uk/forums/showthread.php?t=114630

Now when I attempt to use the soluton stopher has given here and run my report, I get an input box appearing asking me for an inout for TotalTime, which is of course what I am trying to calculate automatically! It's like the expression I am using is ignoring my TotalTime "field" and asking me for the TotalTime.

Can anybody help me out with this?

 

[dufc]

I have uploaded an example of my db, hopefuly that will explain what I am trying to do. Cheers.

 

[Brianwarnock]

Actually it prompts you for TotalHours not Totaltime, you have copied Stophers code verbatim instead of checking field names.

Brian

 

[dufc]

Actually it prompts you for TotalHours not Totaltime, you have copied Stophers code verbatim instead of checking field names.

Brian

Woops, yes I did copy and paste his code and forgot to change it to my own field name.

However, after changing it, I still have the same problem.

 

[dufc]

In fact I now get an error message stating that the expression is typed incorrectly, or it is too complex to be evaluated etc. My expression is now:-

=CStr(Int(Sum(CDbl([TotalTime])*24))) & ":" & CStr(Int((Sum(CDbl([TotalTime])*24)-Int(Sum(CDbl([TotalTime])*24)))*60))

 

[stopher]

I have fixed your report using a variant of my solution (there are other solutions by the way).

A few points here:

• You are looking up the mechanic from the form. But although you see the mechanic name in the form, it's really the mechanic ID that is stored (as defined by Bound Column=1). So in your query you are setting the criteria of MechnicName to a MechanicID value (which obviously won't work). So I've added MechID to the query so this can be used as the criteria.
• The way you calculated TotalTime actually doesn't work although it appeared to. In fact you were calculating a time period measured in days/hours but negative! What I have done is used a calculation that maintains the correct use of the date/time datatype. Essentially I'm adding a day if finishTime is less than startTime.
• By using the Format function in the query you are changing the way the information can be used (since it is now a string instead of a date/time data type). Instead, I have retained the date/time data type and the formatted box on the report.
• I changed the "Total Hours" to "TotalTime"
• I added some formatting to the formula to make it appear more like a time.

It's worth reading about how date/time is managed in Access as it will go some way to explaining what you are dealing with. Try here.

hth
Chris

 

[dufc]

I have fixed your report using a variant of my solution (there are other solutions by the way).

A few points here:

• You are looking up the mechanic from the form. But although you see the mechanic name in the form, it's really the mechanic ID that is stored (as defined by Bound Column=1). So in your query you are setting the criteria of MechnicName to a MechanicID value (which obviously won't work). So I've added MechID to the query so this can be used as the criteria.
• The way you calculated TotalTime actually doesn't work although it appeared to. In fact you were calculating a time period measured in days/hours but negative! What I have done is used a calculation that maintains the correct use of the date/time datatype. Essentially I'm adding a day if finishTime is less than startTime.
• By using the Format function in the query you are changing the way the information can be used (since it is now a string instead of a date/time data type). Instead, I have retained the date/time data type and the formatted box on the report.
• I changed the "Total Hours" to "TotalTime"
• I added some formatting to the formula to make it appear more like a time.

It's worth reading about how date/time is managed in Access as it will go some way to explaining what you are dealing with. Try here.

hth
Chris

Thank you so much Chris!

Like I said in my first post, I am very new to Access, I have only been using it a matter of weeks, and I am learning new things everyday.

I have a couple of books, but I find that having things explained on forums such as this is much more helpful.

I will read the link you gave me there and hopefully start getting my head around the way time and dates are handled in Access.

Thanks again

 

[stopher]

I you see the attachment I post?
Chris