Elapsed time: division? 2

[dr1256dr]

Greetings

I have created an application for use in a production facility, for logging job and task progress, along with elapsed times. Operators "start" tasks, and "end" tasks with a click of a command button, or a swipe of a barcode scanner. The "start" or "end" action runs an append query that populates separate tables, using the time() function.

I then have a listbox, with a query for the source. The query uses both the start and end tables. In the query, I use:

Elapsed: Format([Start]-1-[End],"hh:nn:ss&quot

This displays the "elapsed time" correctly, if I was only concerned with displaying individual elapsed times.

However, I am actually trying to create an actual database of times that can be manipulated, and reported on. Totals, division for averages, etc. From what I can understand, searching through all the threads, Access is not really storing minutes and seconds, but reference points in a 24 hour cycle, even though you can format the results to "look like" elapsed times.

So I am assuming I am going to have to use VBA to achieve this goal. Specifically, I want to be able to take my "elapsed time" and divide it by whole numbers. For instance:

01:15:10 divided by # of doors equals X minutes and X seconds per door. (hh:nn:ss)

Or;


01:15:10 plus 00:32:18 plus 0:49:31 = 02:36:59 (hh:nn:ss)

Etc.....


I hope I am making myself clear! If anyone could point me to a thread, or show me some code examples, I would really appreciate it. I am stuck! Meanwhile, I will keep pouring through the posts.

Thanks....

D

 

[KenReay]

Hi

DAte/Time data types hold the date and time at a point in time, as you say

You can derive an elapsed time using a function like dateDiff() with will return the elapsed time between two points in time in either Minutes, Hours, Days etc (see help for full info), bearing in mind what you are trying to do (ie manipulate elapses times), I would susggest the answer is to save the elapsed times in a suitable unit (eg minutes), you can then add, subtract, average whatever you want, and the answers will be reyurned as a number of minutes (say), you can then of course convert that number to hours and minutes or whatever

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[dr1256dr]

Thanks for the quick response. Please humor me here, as I am learning VBA as I go.

Can you give me a specific example how I would go about this? Would I use the DataDiff function in a query? (I looked in help.) I tried:

Elapsed: DateDiff("n", [Start], [End]) This returns a value that appears to be the minutes, (rounded). I tried "nn:ss", this returns an error.

Then I tried, in 4 new query fields;

Elapsed: DateDiff("s", [Start], [End]) This appears to return the seconds. Then;

AverageSeconds:[Elapsed]/[PieceCount] This appears to return the correct value. Then;

AverageSubTotal:[AverageSeconds]/60 This also appears correct. Then;

AverageFinal: Format([AverageSubTotal],"hh:nn:ss&quot

The AverageFinal result appears to be what I need. The problem is, this fails when start is before midnight, and End is after midnight. Other than that, it appears to work.

Thanks again...

D

(Guess I might have started this in the wrong forum, Sorry)

 

[MichaelRed]

In contrast, I often use the complete date/time and then just treat these as "Double" for this type of calculation. Date/Time (Date Data type) is broken down to the whole number portion representing the number of (Whole) days since 12/30/1899, while the decimal portion reporesents the fractional part of a day.

It is the fractional part which is more 'interesting'. Since a "Whole day" = 1, is it easy to see that a half day = 0.5 days = 12:00:00 Hours. Taken to the limits of the Ms. Date/time formats, 1 second - 1/86400 th of a whole day. It is easy and pratical to use this in many / various ways in just doing simple (4 function) math on the date data type, all withoout the inconvenience of needing to always recognize and remember that datediff only returns the number of (specified) intervals between the points.

e.g.:

? datediff("Y", #12/31/2002#, #1/1/2003#)
 1 
? datediff("d", #12/31/2002#, #1/1/2003#)
 1 

? datediff("Y", #12/31/2002#, #1/1/2003#) =  datediff("d", #12/31/2002#, #1/1/2003#)
True

Which quite logically and even more incorrectly determines that a year and a day are the same?

More to the point of the question, using the times from your example -and assuming these are 'work times' and there were 25 thing made during the sum of these intervals:

? Format((#01:15:10# + #00:32:18# + #0:49:31#) / 25, "h:m:s")
0:6:17

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[dr1256dr]

Thanks, I will try this later.

Dumb question: I added a timer event to my simple form, that fires every second. This appends a 1 to simple temp table. Seems too simple to be true, I now have a number of seconds per operation with which I can do anything I want.

What is wrong with that picture?

Kind of a lazy approach, I know.

Dennis

 

[dr1256dr]

MichaelRed;

I can't seem to do what I need with your example. I modified it, so this is probably where I went wrong.

To recap what I am trying to do:

tblLog has 4 fields: fldJobNo, fldPieceCount, fldStart, and fldEnd.

My form and queries append the fldJobNo with a 7 character text value, the fldPieceCount field with a number, the fldStart field with a time, (using the Time() function), and the fldEnd field with a time, (using the Time() function).

The Data Type properties of that table are set to;

fldJobNo: Text
fldPiecCount: Number (Integer)
fldStart: Date/Time (Long Time)
fldEnd: Date/Time (Long Time)

I then query that table, using

fldElapsedTime: Format([fldStart]-1-[fldEnd],"hh:nn:ss&quot

to get the new field: fldElapsedTime.

In the same query, I use the function you suggested, substituting your hard coded times with the ElapsedTime field;

Average:Format(([fldElapsedTime]/[fldPieceCount]), "h:m:s&quot

I get an error.

Please excuse me if I seem rather slow to grasp this!

If there is a better way to achieve what I am try to do, I am open to suggestions. If some VBA code is the way to go, I am willing to give it a shot if I can get a hint in the right direction.

The big picture: All I want to do is to capture a start time, capture an end time, and then calculate the elapsed time. Then be able to divide that elapsed time by a field in a table, or total all the elapsed times, etc.

Thanks again,

Dennis

 

[MichaelRed]

I would replace the expression for the fldElapsedTime with ==>
fldElapsedTime: [fldEnd]-[fldStart]

And you cannot do the average in the SAME query as the elapsed time. You need an aggregate query which sums the elapsed times and thepiece count and do the Avg/division seperatly.

It would appear that you are reaching somewhat beyond your experience with SQL / Relational database operations. I can only suggest that you visit your local bookstore and get one of the third party text / tutorials on Ms. A. and review the subject(s) thoroughly.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BobJacksonNCI]

D,

Following is from Microsoft Knowledgebase article 210276:

Note in the example that "Interval" is the variable used to pass the Ending minus Starting date/times.

Calculating Time Data
Because a time value is stored as a fraction of a 24-hour day, you may receive incorrect formatting results when you calculate time intervals greater than 24 hours. To work around this behavior, you can create a user-defined function to ensure that time intervals are formatted correctly.

To calculate and format time intervals correctly, follow these steps:
1. Create a module and type the following line in the Declarations section if it is not already there:
2. Option Explicit
3. Type the following procedure:
4. '------------------------------------------------------------------
5. ' This function calculates the elapsed time between two values and
6. ' formats the result in four different ways.
7. '
8. ' The function accepts interval arguments such as the following:
9. '
10. ' #5/12/95 6:00:00AM# - #5/11/95 10:00:00PM#
11. '
12. ' -or-
13. '
14. ' [End Time]-[Start Time]
15. '------------------------------------------------------------------
16.
17. Function ElapsedTime (Interval)
18. Dim x
19. x = Int(CSng(Interval * 24 * 3600)) & " Seconds"
20. Debug.Print x
21. x = Int(CSng(Interval * 24 * 60)) & ":" & Format(Interval, "ss&quot _
22. & " Minutes:Seconds"
23. Debug.Print x
24. x = Int(CSng(Interval * 24)) & ":" & Format(Interval, "nn:ss&quot _
25. & " Hours:Minutes:Seconds"
26. Debug.Print x
27. x = Int(CSng(Interval)) & " days " & Format(Interval, "hh&quot _
28. & " Hours " & Format(Interval, "nn&quot & " Minutes " & _
29. Format(Interval, "ss&quot & " Seconds"
30. Debug.Print x
31.
32. End Function

33. Type the following line in the Immediate window, and then press ENTER:
? ElapsedTime(#6/1/1999 8:23:00PM#-#6/1/1999 8:12:12AM#)
Note that the following values are displayed:
43848 Seconds
730:48 Minutes:Seconds
12:10:48 Hours:Minutes:Seconds
0 days 12 Hours 10 Minutes 48 Seconds


HTH,
Bob
Your mileage may vary, but following the guidelines in faq181-2886 will help you reach your goal.

 

[dr1256dr]

MichaelRed;

You are absolutely correct! I frequently reach beyond my experience..... By doing so, and browsing great forums such as this, I end up learning something new everytime!

Thanks for your help, and a star for trying to help.

Dennis

 

[dr1256dr]

BobJacksonWcom;

Thanks a bunch. This is exactly the kind of tip I was looking for. I will adapt this to fit my needs, and post how I used it when I get there.

Dennis