Calculating Time

[BIMguy1]

Hello
I have a simple DB that tracks time of a task with a stopwatch HH,MM,SS. Start and Stop
I can't figure out how to calculate the elapsed time and record it in a table.
Any ideas?

 

[Minty]

Don't store it, because you can always calculate it.

Elapsed time : CDate(StopTime - StartTime)

If any of your start or stop times get changed you have to try and capture that and update your stored value.

 

[BIMguy1]

Don't store it, because you can always calculate it.

Elapsed time : CDate(StopTime - StartTime)

If any of your start or stop times get changed you have to try and capture that and update your stored value.

I have to store it for an exported report. The start and stop times should never be altered.

 

[The_Doc_Man]

@BIMguy1 - first, understand that Date variables are actually date/time variables - they store times as a number in DOUBLE format. The precision of this format lets you express dates and times to one second very easily. (In fact, the variable can store more precisely than that, but Access date/time formatting routines won't handle fractions of a second.)

The format of the "date" number is that days are whole numbers (integer part of the double) and time is fractions of a day, with midnight as fraction value 0. The number for a day is stored as "days since a reference date" which, for Access makes day 1 equal to 31-Dec-1899. Why they picked that particular reference date is anyone's guess, but there it is. But the practical side of it all is that the difference between two dates IS the elapsed time between them. Access will be able to compute the difference and display that difference with a displayed precision of 1 second. Minty's suggested computation is spot-on.

Now, as to "have to store it for an exported report" ... no you don't. Reports can be driven from queries just as well (in fact, often BETTER than tables). So drive the report from a query. OR define the report's "elapsed time" field as an expression, which also works perfectly well. If this report involves an export of a file, you can export query results to spreadsheets and you can export any report to a file. You almost NEVER have to store a computed value if you are keeping the values from which it is derived. Further, per your comment that the start/stop times would never be altered, you have exactly the case where you don't need to store the computed value, too.

Working with Access sometimes seems counter-intuitive, but the rule is that you don't store anything that you could easily compute on-the-fly. There are exceptions, but because you are keeping the two times on which your result is based, your case is not one of the exceptions.

 

[BIMguy1]

@BIMguy1 - first, understand that Date variables are actually date/time variables - they store times as a number in DOUBLE format. The precision of this format lets you express dates and times to one second very easily. (In fact, the variable can store more precisely than that, but Access date/time formatting routines won't handle fractions of a second.)

The format of the "date" number is that days are whole numbers (integer part of the double) and time is fractions of a day, with midnight as fraction value 0. The number for a day is stored as "days since a reference date" which, for Access makes day 1 equal to 31-Dec-1899. Why they picked that particular reference date is anyone's guess, but there it is. But the practical side of it all is that the difference between two dates IS the elapsed time between them. Access will be able to compute the difference and display that difference with a displayed precision of 1 second. Minty's suggested computation is spot-on.

Now, as to "have to store it for an exported report" ... no you don't. Reports can be driven from queries just as well (in fact, often BETTER than tables). So drive the report from a query. OR define the report's "elapsed time" field as an expression, which also works perfectly well. If this report involves an export of a file, you can export query results to spreadsheets and you can export any report to a file. You almost NEVER have to store a computed value if you are keeping the values from which it is derived. Further, per your comment that the start/stop times would never be altered, you have exactly the case where you don't need to store the computed value, too.

Working with Access sometimes seems counter-intuitive, but the rule is that you don't store anything that you could easily compute on-the-fly. There are exceptions, but because you are keeping the two times on which your result is based, your case is not one of the exceptions.

Hi
Lets start with, I'm a rooky at this and pretty much everything you just said is way over my skill level.
I'm want to store the elapsed time in a table so I can then import that data into a Power BI template.
Here is what I have. Please help

 

[The_Doc_Man]

Can Power BI import a query? If so,

SELECT ID, Start, Stop, [Employee Name], [Start Date], [Spool Name], Comments, (Stop - Start) As [Elapsed Time]
FROM [Pipe Spool Data]
ORDER BY ID ;

You could build this query using the Create Query builder and going into SQL view to type it in. Then if Power BI will import a query, import THAT one.

Since you are a rookie, here are a couple of gentle reminders, suggestions, etc.

1. Don't include spaces in the names of anything. Run the words together. If you do that, you can refer to the names without using square brackets.
2. Using a field name like ID is of limited mnemonic value.
3. If the Start Date is always the same as the date from the date/time in the Start field, you can change drop the [Start Date] from the table and then change the query to give it to you in another format.

This sample implements suggestions 1 & 3 but I won't presume to change field names in this case.

SELECT ID, Start, Stop, EmployeeName, CDate( CLong( Start ) ) As StartDate, SpoolName, Comments, (Stop - Start) As ElapsedTime
FROM PipeSpoolData
ORDER BY ID ;

This demonstrates that queries can do things on-the-fly very easily, reformat data, and do computations. You should quickly learn something that will help you tremendously down the line: In Access, queries are one of your best workhorses for data manipulation. They are incredibly powerful and can make your life SO much better. For reports, ANYTHING you could report from a table can be reported from a query that allows you to do all sorts of computations behind the scenes. There are arguments for basing forms off of queries, too, when either computation or presentation order becomes important.

 

[plog]

We've all heard your reasons for you thinking you need to store calculated values and we all reject them. Perhaps a 3rd person telling you not to store calculated values will be the one to convince you:

You do not store values that can be calculated--you calculate them.

That means ElapsedTime should not be a field in your table, but a field in a query. Then when you want to reference it, you look to the query. Further, the field StartDate should not exist in your table either since it to is calculable from Start.

 

[BIMguy1]

We've all heard your reasons for you thinking you need to store calculated values and we all reject them. Perhaps a 3rd person telling you not to store calculated values will be the one to convince you:

You do not store values that can be calculated--you calculate them.

That means ElapsedTime should not be a field in your table, but a field in a query. Then when you want to reference it, you look to the query. Further, the field StartDate should not exist in your table either since it to is calculable from Start.

I'm not sure how to respond to this so I'll just say thank you

 

[Minty]

@BIMguy1 If you copy and paste the bottom query the @The_Doc_Man into the SQL view of the query designer and run it you will see it gives you exactly what you need.

No need to try and run updates on any tables.

You can then use that query in Power BI as a data source. It will always be accurate for the base data in your table.