Start / Stop calculations

[Ms Kathy]

I want to enter start times and stop times for each process done on multiple production lines. The data to be entered includes: StartTime, EndTime, and ProcessCode (Process codes being: "producing", "down time", "lunch time", "changeover", etc.) We want to see where the time is being spent during work days. I want the end result to be reporting that shows how many hours/minutes were used on each process and each production line by date or date range; including summaries. I'm now setting up the tables and am finding after much research that date/time fields can be quite complicated when calculating. A few questions come to mind. 1) Am I correct that the StartTime field and the EndTime field data type to be date/time, and the format to be general date? 2) Can the data entry be easily done on a form without having to enter date, time for each entry? 3) How do I accommodate for a start time of 11:00 pm on 5/5 and an end time of 2:00 am on 5/6? Can anyone guide me as to where I might get some specific instruction related to this project? I am getting bleary eyed and mind numbed trying to decipher everything. Please keep in mind that I am not just a novice, but really really a novice. Thank you for your consideration!

 

[Solo712]

I want to enter start times and stop times for each process done on multiple production lines. The data to be entered includes: StartTime, EndTime, and ProcessCode (Process codes being: "producing", "down time", "lunch time", "changeover", etc.) We want to see where the time is being spent during work days. I want the end result to be reporting that shows how many hours/minutes were used on each process and each production line by date or date range; including summaries. I'm now setting up the tables and am finding after much research that date/time fields can be quite complicated when calculating. A few questions come to mind. 1) Am I correct that the StartTime field and the EndTime field data type to be date/time, and the format to be general date? 2) Can the data entry be easily done on a form without having to enter date, time for each entry? 3) How do I accommodate for a start time of 11:00 pm on 5/5 and an end time of 2:00 am on 5/6? Can anyone guide me as to where I might get some specific instruction related to this project? I am getting bleary eyed and mind numbed trying to decipher everything. Please keep in mind that I am not just a novice, but really really a novice. Thank you for your consideration!

Hi Ms Kathy,
You can start by looking into the DateDiff function E.g. Datediff("n",[start],[stop]) will give you the difference between to date stamps in minutes. It does not matter if the dates are different.

The "n" parameter is for minutes, "h" is for hours, "s" is for seconds.

Best,
Jiri

 

[gemma-the-husky]

first - I would treat this like a clocking in/clocking off procedure and have a table structured like this, so you get a date/time sequence of all your events

processID, event code, date+time

My idea would be to validate the data before trying to use it, so you can identify and fix any record errors.

--------------------------------------------------------
I would 100% not have a table like this, with start and stop times on the same row

date, process, start time, stop time, as that will be a nightmare to control.
---------------------------------------------------------

so for a simple start/stop you get

PROCESS , EVENT , TIME
process1 , Start , May 3rd 10.00pm
process1 , Stop , May 3rd 10.00pm

and not

PROCESS , START EVENT , STOPEVENT
process 1 , May 3rd 10.00pm , May 3rd 10.00pm


the event time needs to be recorded with both date and time, to allow for the midnight rollover. if easier, do these separately, although there is no need to. Designing a form to manage both date and time just takes a bit of care. Date pickers are easy, but time pickers not.

now all the events are stored in sequential time order, so for a given process you can check that these are what you expect. you will need code for this, not a query. You need to validate that dates/times are logical, not out of sequence, and that you have a matching pair for each action

ie start
break start
break end
stop

Thus starting with a "start time" event, make sure that every subsequent process is logical. ie - you don't have another start time without a stop time, and all the events to the next time make sense.

once you know the data is "safe" to use, you can evaluate the time gaps between relevant events by doing date+time (event 1)- date+time (event 2), and mark the events as "processed"

for each complete process set you can then store that process summary in a different table.

processID, date, total time elapsed

now you can analyse these very easily.

 

[Ms Kathy]

When you say: "first - I would treat this like a clocking in/clocking off procedure and have a table structured like this

process, event code, date+time"

Do you mean "process, event code, start date/time, AND end date/time"?

 

[Ms Kathy]

I need to print out your reply and study it more thoroughly so I can comprehend. This may take a while. Thank you kindly.

 

[plog]

1. Yes, you want a general date because you need to capture both date and time in this field.

2. Define 'easily'. Its a date/time field, yet you don't want to enter date/time? Explain how you envision data getting into the system.

3. By capturing the date and the time in the fields.

Date calculations really aren't that hard, there's built in functions for doing it: http://www.techonthenet.com/access/functions/. The one thing to keep in mind though is that time calculation is a different than time presentation. Just because you want to show the user "12 days, 20 hours, 2 minutes", doesn't mean that effects how you calculate data. A general rule is to do your calculations in the lowest unit you want to report on and then build another function to convert that total number of base units into the format you want to display e.g. Format_Minutes(18482).

 

[Ms Kathy]

Regarding 2) I was hoping to make data entry easier since there are so many entries to make each day. I tried doing it myself and find I need to enter month/day, hour (colin) time, and am or pm. Just a lot to enter that's all. I will check out the link you provided for further research. Thank you kindly!

 

[gemma-the-husky]

When you say: "first - I would treat this like a clocking in/clocking off procedure and have a table structured like this

process, event code, date+time"

Do you mean "process, event code, start date/time, AND end date/time"?[/QUOTE]


Red bit. definitely not

store the events downwards sequentially. The entry order doesn't matter.
The only exception might be is if in every case you were presented with the start and finish times of each activity at the same time, in which case you might be able to use the structure you suggested. but if you get a sheet showing this sort of thing, its probably easier to just enter this directly, rather than manually compute all the elapsed times.

sample worksheet
start 9.00
break 10.30 end break 11.00
lunch 12.30 lunch end 13.15
break 15.00 end break 15.20
stop work 17.55


process1234 start 10.00
process1235 start 11.00
process1236 start 11.30
process1234 break 14.50
process1235 stop 16.00
process1234 stop 17.00 'this should error - see note below
process1236 stop 10.00 next day



'entered afterwards following investigation of the error
process1234 break 15.20

then you can sort this by processID, and date/time ascending, and validate the sequence. In the above there should be an error as we have a work stop on the red process, but no break stop

so now you can evaluate the time elapsed of each activity

eg - the red process
process1234 date 4.50 hours - work - start to break
process1234 date 0.30 hours - break start to break end
process1234 date 2.10 hours - work -break to stop

and now you can easily construct queries to analyse total break time by process//project/employee/task/whatever - it just depends what the process actually controls.

-------------
out of interest, how does the info come to you? On manual workbooks or something? if it's in a spreadsheet you can probably automate a lot of this. Manual entry is bound to be a pain, I expect.

if you are doing it manually then you can enter an "active date", and as you enter each event you can assume the date is the "active date". Maybe have a button for "tomorrow" and "yesterday" to adjust the date by one either way.

you enter time in a bound time field as simply 10.50 (50 minutes)

 

[Ms Kathy]

This will take a bit to wrap my head around this . . .

 

[Ms Kathy]

It seems like there will need to be a lot of manual verifying of the data.

 

[gemma-the-husky]

well, only if it is not reliable. If the data sequences correctly, you don't have to do anything.

That's why I mentioned clock cards. - just clock in, clock out.

If you get two consecutive clock card types of the same type, there is a problem, as either a clock in, or clock out is missing.

if the sequence is intact, you can just process the records with no manual intervention.