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!
Start / Stop calculations
- Thread starter Ms Kathy
- Start date
Solo712
Registered User.
- Local time
- Yesterday, 23:47
- Joined
- Oct 19, 2012
- Messages
- 838
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
- Local time
- Today, 04:47
- Joined
- Sep 12, 2006
- Messages
- 15,984
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.
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.
Last edited:
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"?
process, event code, date+time"
Do you mean "process, event code, start date/time, AND end date/time"?
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).
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).
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!
- Local time
- Today, 04:47
- Joined
- Sep 12, 2006
- Messages
- 15,984
Last edited:
- Local time
- Today, 04:47
- Joined
- Sep 12, 2006
- Messages
- 15,984
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.
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.
