Scheduling DB (Access beginner)

[Mr listermint]

Hello All,

Trying to put together a scheduling database for machine in a factory. The machine is operated on a shift pattern. Idea is to create job and for the schedule to work out the jobs start time from the previous job, the job duration derived from no of copies & speed of machine, the job end time based on the job duration and the shift pattern. I have three tables:

tbl_job
tbl_machine_schedule (FK tbl_machine_schedule.job_id -> tbl_job.id)
tbl_machine_shift

Ideally when a job is related to the schedule the schedule should lookup the job end time against the machine shift, if the job end falls in non productive time this time is added to the job duration to get the proper end date and time.

I am not sure the best way to approach this, is this possible using calculated fields & queries alone or will it be best too delve into macros/VBA

All advice welcomed.

[NTC]

its tricky. you are self described as a novice - and this is fairly advanced.

one needs to start with a baseline time set for a job generically. probably in minutes.

then if the machines have differing speeds - you apply that to the baseline as a multiplier. Let's say 2 machines are the same speed and 1 machine is 20% faster...then if using either of the 2 machines the multiplier is 1 but if the third machine the multiplier is .8 (times the baseline job minutes); and this gives you the machine minutes.

then you need to apply the job machine minutes into a table; and probably with a start time, then time/date add the minutes to calc the finish time.

and go from there....

hope it helps.

[dblife]

With the correct table structure it should be quite straight forward.
I would not try to weave a shift table into it though.
If you know the shift start and finsh times, build them into an 'iif' function in a query to calculate the shift on the fly.
Is cycle time tied to job? eg. Part A always runs at 36 seconds and part B always runs at 24.7 seconds.
Also, are you wanting to make an allowance for changeover time?

[dblife]

Do you want help building this db?

[Mr listermint]

Thanks for the advice both, as you NTC says it is tricky. Thanks for the offer help dblife.

Yes the machines can run at different speeds, not too worried about factoring in a shift change over allowance. The part I am concerned about is factoring in the non working period, for example a job will take 8hours and starts 4 hours before the shift end on Friday, it will therefore complete 4 hours after the next shift start on Monday morning.

My plan is to create a time table (tbl_machine_shift) with a row per day and a reference for the productive and non productive periods (can accommodate overtime this way), I assume access stores Date/Time in the same way as excel (double byte). By hook or by crook the job end time will be calculated from the previous job end time + current job duration. This value will be looked up against the schedule and if it falls into a non productive period it will need to include this period in the job duration and iterate until it finds the next productive period.... easy heh!... As the time table will grow as time romps on (bit like my waist line) will I need to worry about a maximum number of rows?

The next job will always follow on from the previous job end so I guess I need to know how to reference this value in the previous row and use it in calculations. As I am typing I am guessing with the need for variables this is now leaning toward VBA stuff.

Thanks for the advice again, if I manage to get somewhere I'll post a copy up.