Need Advice for Job Timestamping

[sjd]

I'll start off by saying I am unsure if this should be in tables or queries section of the forums. In case it matters, I am using office 2007 (accdb format) for the sub database I am working on (which links to an office 2000 mdb format, for a few fields of information -- some of the id's are in linked tables).

I am trying to store job timestamps for each job. Each job goes through several departments (Milling, Grinding, etc). Within each department, it can go through different operations (Setup, Programming, Machining, etc). Each operation can start and stop multiple times.

The table layout I have been using thus far as an idea:
ticket_ID - time ticket # (primary key, auto number)
job_ID - the job work is being done on
department_ID - department work is being done in.
operation_ID - type of work being done (setup, programming, machining)
start - [Yes/No] Yes if starting work, no if stopping work.
employee_ID - Person doing the work.
made - qty parts made
scrap - qty parts scrap
reason - why they were scrapped
notes - other job notes
timestamp - Date/Time - Default =Now()

This stores all the start / stop times -- even accounting for multiple starts/stops throughout the day or week as needed, but I can't seem to figure out how you would now query/calculate the hours per job per department.

I thought I'd post this here, since my table design may be flawed. I'm building this and can change it completely, so this is the time to change it if the table needs rearranging. If it is good, hopefully someone can help me with the query part; or I can move the thread there.

Any help getting this working will be very appreciated.

 

[spikepl]

I would have a StartTime and StopTime on the same record -that would make life much easier. Right now how do you set the Start Yes/No and how do you ensure - if this is set by a user - that the right value is set? That would also be easier to tackle with dedicated StartTime and StopTime fields -the Start field would be redundant.

The value for StopTime could then be set as default value for that field in the form.

 

[sjd]

Currently, the Yes/No is a check box. The user would scan the proper bar code on the job form (entering Job#, Department #, Operation #). Then check the start box (or leave it blank if closing a job), select user, etc. This creates 1 entry per start and 1 per stop. Which is largely the confusion on how to go about actually querying the time information.

I can see how doing a set start/stop as 2 entries in one row would make things infinitely easier. I am just not sure how to make sure the start/stops work correctly, given that you can have the same person starting / stopping a job numerous times throughout the day.

Am I correct that you would need a have a Start Job button that inserts a new record. (Possibly checking for an unclosed previous record for same part/department/user to notify them it was not closed -- in case they hit the wrong button.) Also, a Stop Job button that updates the time stamp of the last record for that job/department/operation/user? (Which again, notifies them if a record does not exist -- and checks to see if that was the correct button.)

 

[spikepl]

Hard to say for me. If a job is open, then it doesn't make sense to allow the system to reopen an open job, so the code logic should default to StopTime, or the job should not be selectable for Opening ... or or or ... depends on your work cycle. I reckon automation is better than relying on a user having to remmeber to click a checkbox, and soemtimes getting it wrong.

AS to StartJob button - IDK - depends on how the lot is put together. Somehow the user must indicate what he want to do, I presume. But you should not be able to create a new record relating to a job which is not closed.

 

[sjd]

If the system automatically handled it - If open job exists, close it (filling in StopTimeStamp, qty made, qty scrapped, etc.). Else, insert new record.

How would you handle forgetting to clock in or out of a job previously?

My guess is to have a report that prints difference between start/stop over 10 hours (or the longest shift duration) as well as entries without a StopTimeStamp. Such an entry would like be job accidentally punched out in the morning, instead of punched in.

I just want to make sure I have a plan in place in case of error. =) I also want to make it as simple to use as possible.

 

[spikepl]

There are so many business rules involved in this so I cannot say.

Design, on paper, a perfect work flow. You can make Access do anything bar read minds. Then work through the workflow, including all possible mistakes, and find out how to deal with them. A starting point would be to examine the existing workflow, and how it deals with missing or redundant information, glitches and exceptions. The idea is not to automate the existing system but to learn what happens IRL.

Some mistakes you can prevent altogether, whereas other can be resolved automatically (jobs could close by midnight, etc...), or need to be brought to someone's attention.

The thinking is your job - that bit we cannot help you with.

But we can always abuse your choices after you made them

In general: anything that the system can work out by itself, it should. No point asking users about more than necessary. But there are often exceptions, and they are the ones that set the boundaries for how "clever" the application can be.

 

[sjd]

Google Search "Access run query daily" first response "Scheduling a Daily query to run at midnight - Microsoft Access / VBA". I swear it reads minds sometimes.

Being able to close accidentally open jobs is the only thing I can think of that would be a big issue to data entry. Other things can be handled in the form itself [making required fields required, etc.].

Of course, I'll see how it goes when doing some actual beta testing.

Thank you for all the help. I'll have to rearrange things a little and test it out.