Question Job Timing Database

[MattBaldry]

I am in the process of implementing a database that will monitor time spent on different processes of our production.

At the moment I have a table, tblActivity. This table has the ItemID related to the job, ActivityID (autonumber), Date (time/date the process was recorded), Status (the activity type) Location (where in the factory it is),Employee (the person doing the process) and Notes.

For production I have 4 status types, (Start, Complete, Hold, Resume).

Would it be more practical, to calculate time spent, to have each activity with 4 date/time fields for start, complete, hold, resume rather than 4 status types each with a single date/time field?

I have not gone live yet so not enough data to analyse, but if someone has built something similar I would be grateful to hear what they did.

If I have not explained myself well enough, let me know.

~Matt

 

[Dreamweaver]

Im at work at the moment but will look when help when home i have developed a number of project, production management systems will see if I can put some tables together

 

[MattBaldry]

Im at work at the moment but will look when help when home i have developed a number of project, production management systems will see if I can put some tables together

Mick, thanks for the reply. I have the database itself built. It is more a question of the best way of managing the timings. The more I think about it, the more I think the activity with 4 time fields might be easier when analysing the data.

~Matt

 

[isladogs]

Do the second method creating 4 separate records each with a StatusType and StatusDateTime field.

The first method would not be normalised and cause you issues later.
Whereas the second would still work if you need to add further types like Review later.

 

[MattBaldry]

Do the second method creating 4 separate records each with a StatusType and StatusDateTime field.

The first method would not be normalised and cause you issues later.
Whereas the second would still work if you need to add further types like Review later.

This is how I have it at the moment. I suppose I could try it for a month or so to get the data and see how it looks after that.

~Matt

 

[Minty]

In an ideal world you would store them as separate entries.
I've done both for process timings and they can both present some challenges depending on if you ever get missing steps in the process or not...

Sometimes a "in-between" storage may be preferable. So have a ProcessTypeField and a stop and start for that entry. That way you can easily query the current process as the record won't have an end time, but if for some reason a part of the process is missed or not required or you add another type of process your data structure still works.

The fact that your end time for stage 1, will normally be the same as the start time for stage 2 shouldn't matter.

 

[isladogs]

You mean you have the second method now? If so keep it.
If you have the first method, don't wait a month. Change it now.

 

[MajP]

Does a process have the same location and same Employee for each status? Or can employee 1 start and employee 2 finish. Can it start at location A and finish in location B? If so then you are stuck with your original design for a record for each status.

 

[MattBaldry]

Does a process have the same location and same Employee for each status? Or can employee 1 start and employee 2 finish. Can it start at location A and finish in location B? If so then you are stuck with your original design for a record for each status.

As it stands, the activity has a date, location, status, employee and notes.

"Bob" would add the activity as Production (Start), Production (Area), Bob as the employee.

When complete, "Bob" would add a new activity as Production (Complete), Inspection, Bob.

When inspection pick up the job, they then do Inspection (Start), Inspection Area, Bill.

So the production start to complete would use 2 activity records as opposed to have a single record with 4 dates.

~Matt

 

[MajP]

It looks definitely like your initial design is correct. It appears not all processes use all categories (did not show hold resume), and could it be possible to use more than one category. Can you go to Hold, then Resume, and then go Hold, Resume? If so even more support for your original design.

 

[MattBaldry]

It looks definitely like your initial design is correct. It appears not all processes use all categories (did not show hold resume), and could it be possible to use more than one category. Can you go to Hold, then Resume, and then go Hold, Resume? If so even more support for your original design.

Yes, a job can go from hold to resume many times before Production (Complete). Once a job has been finished I think need to work out how much time was spent on the overall process of production minus any "on hold" periods.

The vast majority of jobs will only go from start to complete though.

~Matt

 

[MattBaldry]

I now have a working database, and the employees are starting to log their jobs correctly. I now need some code help to work out the production time spent on the job. As an example, below are the status and times of a completed job.

Production (Start) 12:31 - *Note* production time starts here
First Off (Start) 13:36 - *Note* production time pauses here, first off time starts
First Off (Complete) 13:43 - *Note* first off time stops here
Production (Resume) 13:50 - *Note* production time resumes here
Production (Complete) 14:38 - *Note* production time stops here

By my calculations the time spent in production was 83 minutes (there is a 30 minute break from 13:00 to 13:30).

Time spent in first off was 7 minutes.

I have used a timing code posted by a Grumm, I cannot post the thread as I have not had 10 posts yet.

I am after some help to have this timing code work out the different stages of each job. At the moment, this code shows 127 minutes in production as it hasn't taken away the lunch break of first off times.

~Matt