Production Scheduling Database

[Barrett]

I am currently trying to create a new database for our prodcution schedule.
Our current schedule is in excel and is very visual but since it is in excel,
reports and updating demand and production numbers is difficult. In addition,
adding new part numbers is very time consuming.

I have started to build a database in access.

I basically have three tables: Item master: Fields are part#, manufacturing
cell, and cycel time.
Demand: This is updated weekly from our customers. The fields are part#, qty,
due date.
Schedule: Part#, qty and date are the only fileds needed.

My problem is pulling these into a form with the dates aligned across the top
of the form, with the schedule qty and demand qty that align with those dates
below. I basically would like something like a pivot table, but one where I
could change the data in the scheudle line to ensure I meet demand. This may
sound simple, but I obviously have hit a wall. It is very important that the
schedule be visual so I can easily see shortages prior to releasing the
schedule. With ~250 finished good part#s, I need to rely on the system to
easily show me these shortages.

 

[Tom van Stiphout]

I have more questions than answers at this time:
1: For the sake of this discussion, are ManufacturingCell and
CycleTime important? If yes, how?
2: Curious that tblDemand does not include CustomerID. Isn't it more
important to meet a big customer's demand than a small one's?
3: How is tblSchedule filled? Manually by a Scheduler, or by the app?
4: What is tblSchedule.Date? BTW, don't use Date for a field name
because it is a reserved word. Perhaps ScheduleDate?
5: I don't see where you are storing the actual production. Where is
tblProduction: PartNo, Qty, DateProduced? Can the machines report
production to your app automatically?
6: Reading between the lines I get the impression you only want to
know that the Scheduled quantities meet the Demand quantities. But
that seems a trivial expression: ScheduledQty = DemandQty -
ProducedQty.

-Tom.
Microsoft Access MVP

 

[Barrett]

Good questions. 1) For the main form for scheduling production, the cell and
cycle time are not necessarialy important, but will be used to evaluate the
level loading of the schedule and the overtime requirements and thus help
"move" parts and people around to meet demand. 2) The demand table does
include the customer ID. I did not include it because it is not actually used
in our actual schedule. Our products are for Lexus, Toyota and Mercedes so
there is not an option to miss a shipment. We do however put more emphasis on
the North American JIT customers with a short pipeline. If we miss a Japan
customer, we can always fly the material if needed although not optimal. In
our current schedule, we simply color the North American part#s to highlight
the urgency. 3) Currently the schedule is populated by the scheudler manually.
However, the excel table goes out for 5 weeks, so patterns are established
and each week we "roll" the schedule by copying next week's schedule qtys on
top of this weeks, leaving the 2nd through the 5th week intact. The scheduler
will add or subtract from the current week based on current demand. I plan to
automatically populate the schedule with the proper amount of material to
meet demand and maintain inventory turns at an optimal level. That will be a
later step and should be fairly easy. the scheudler can then make manual
adjustments when needed. 4) The schedule date would be the date the cell or
line will manufacture the goods. 5) I forgot to mention that we have an
automated report that comes out every morning with the previous day's
production. This is used to update a perpetual inventory in the current
schedule. That way, we can visually see the shortages daily to help
prioritize the lines. This is automated in the excel sheet, but the scheduler
must manually remove the qtys produced from the scheudled qty to provide a
fresh schedule daily. 6) You have read through the lines pretty well although
there are certain lot sizes that need to be run in some cases and a rule of
thumb to hold ~2days worth of inventory on hand to meet demand fluctuation.
Not sure if this posting will allow jpgs to be attached, but I could do that
to show you the current schedule. The visual part of this schedule is great,
but there are ~15 tabs on this thing and can be "messed up" pretty easy. We
also, like a lot of companies, run into issues when the scheudler is out.
That is when I have to perfrom this function and it is very time consuming
and tedious. I hope to make this simple and easy to maintain. Sorry for the
long explaination, but wanted to ensure I answered your questions effectively.
Thanks in advance for your help.

I have more questions than answers at this time:
1: For the sake of this discussion, are ManufacturingCell and
CycleTime important? If yes, how?
2: Curious that tblDemand does not include CustomerID. Isn't it more
important to meet a big customer's demand than a small one's?
3: How is tblSchedule filled? Manually by a Scheduler, or by the app?
4: What is tblSchedule.Date? BTW, don't use Date for a field name
because it is a reserved word. Perhaps ScheduleDate?
5: I don't see where you are storing the actual production. Where is
tblProduction: PartNo, Qty, DateProduced? Can the machines report
production to your app automatically?
6: Reading between the lines I get the impression you only want to
know that the Scheduled quantities meet the Demand quantities. But
that seems a trivial expression: ScheduledQty = DemandQty -
ProducedQty.

-Tom.
Microsoft Access MVP

I am currently trying to create a new database for our prodcution schedule.
Our current schedule is in excel and is very visual but since it is in excel,

[quoted text clipped - 17 lines]

schedule. With ~250 finished good part#s, I need to rely on the system to
easily show me these shortages.

 

[Tom van Stiphout]

Good answers.
I think you are looking for a form with 14 columns, two for each day
(Demand, Scheduled) times 7 days (or whatever number of days is
important to look ahead), plus one column for PartNo, and then the
parts going down for perhaps 250 rows. Perhaps a final column with
some ProjectedOnHand result.
This I would implement with a "temp" table, which is really a
permanent table with (in this example) 16 columns that I would fill
programmatically (Delete query followed by Append query(-ies)) from
the "base" tables. I would probably use Continuous Forms and bind the
form to the table. Then allow any editing needed. Also look at
Conditional Formatting to color certain situations with a background
color.
Then when the user clicks the "Save to Base Tables" button, I would do
just that.

-Tom.
Microsoft Access MVP

Good questions. 1) For the main form for scheduling production, the cell and
cycle time are not necessarialy important, but will be used to evaluate the
level loading of the schedule and the overtime requirements and thus help
"move" parts and people around to meet demand. 2) The demand table does
include the customer ID. I did not include it because it is not actually used
in our actual schedule. Our products are for Lexus, Toyota and Mercedes so
there is not an option to miss a shipment. We do however put more emphasis on
the North American JIT customers with a short pipeline. If we miss a Japan
customer, we can always fly the material if needed although not optimal. In
our current schedule, we simply color the North American part#s to highlight
the urgency. 3) Currently the schedule is populated by the scheudler manually.
However, the excel table goes out for 5 weeks, so patterns are established
and each week we "roll" the schedule by copying next week's schedule qtys on
top of this weeks, leaving the 2nd through the 5th week intact. The scheduler
will add or subtract from the current week based on current demand. I plan to
automatically populate the schedule with the proper amount of material to
meet demand and maintain inventory turns at an optimal level. That will be a
later step and should be fairly easy. the scheudler can then make manual
adjustments when needed. 4) The schedule date would be the date the cell or
line will manufacture the goods. 5) I forgot to mention that we have an
automated report that comes out every morning with the previous day's
production. This is used to update a perpetual inventory in the current
schedule. That way, we can visually see the shortages daily to help
prioritize the lines. This is automated in the excel sheet, but the scheduler
must manually remove the qtys produced from the scheudled qty to provide a
fresh schedule daily. 6) You have read through the lines pretty well although
there are certain lot sizes that need to be run in some cases and a rule of
thumb to hold ~2days worth of inventory on hand to meet demand fluctuation.
Not sure if this posting will allow jpgs to be attached, but I could do that
to show you the current schedule. The visual part of this schedule is great,
but there are ~15 tabs on this thing and can be "messed up" pretty easy. We
also, like a lot of companies, run into issues when the scheudler is out.
That is when I have to perfrom this function and it is very time consuming
and tedious. I hope to make this simple and easy to maintain. Sorry for the
long explaination, but wanted to ensure I answered your questions effectively.
Thanks in advance for your help.

I have more questions than answers at this time:
1: For the sake of this discussion, are ManufacturingCell and
CycleTime important? If yes, how?
2: Curious that tblDemand does not include CustomerID. Isn't it more
important to meet a big customer's demand than a small one's?
3: How is tblSchedule filled? Manually by a Scheduler, or by the app?
4: What is tblSchedule.Date? BTW, don't use Date for a field name
because it is a reserved word. Perhaps ScheduleDate?
5: I don't see where you are storing the actual production. Where is
tblProduction: PartNo, Qty, DateProduced? Can the machines report
production to your app automatically?
6: Reading between the lines I get the impression you only want to
know that the Scheduled quantities meet the Demand quantities. But
that seems a trivial expression: ScheduledQty = DemandQty -
ProducedQty.

-Tom.
Microsoft Access MVP

I am currently trying to create a new database for our prodcution schedule.
Our current schedule is in excel and is very visual but since it is in excel,

[quoted text clipped - 17 lines]

schedule. With ~250 finished good part#s, I need to rely on the system to
easily show me these shortages.

 

[Barrett via AccessMonster.com]

I think you are getting closer to my "vision". The only problem I see with
this is that the columns will be "hard coded" and will not actually be equal
to a date. Hard to explain but easy to see. Any way I could send you the
current schedule so you could view it and see what I am actaully up against?
My goal is to make this visually similiar but fast and easy to update and
publish. If you do not have time I understand. Thanks in advance.

Good answers.
I think you are looking for a form with 14 columns, two for each day
(Demand, Scheduled) times 7 days (or whatever number of days is
important to look ahead), plus one column for PartNo, and then the
parts going down for perhaps 250 rows. Perhaps a final column with
some ProjectedOnHand result.
This I would implement with a "temp" table, which is really a
permanent table with (in this example) 16 columns that I would fill
programmatically (Delete query followed by Append query(-ies)) from
the "base" tables. I would probably use Continuous Forms and bind the
form to the table. Then allow any editing needed. Also look at
Conditional Formatting to color certain situations with a background
color.
Then when the user clicks the "Save to Base Tables" button, I would do
just that.

-Tom.
Microsoft Access MVP

Good questions. 1) For the main form for scheduling production, the cell and
cycle time are not necessarialy important, but will be used to evaluate the

[quoted text clipped - 57 lines]

 

[Tom van Stiphout]

On Tue, 02 Dec 2008 14:44:42 GMT, "Barrett via AccessMonster.com"

Go ahead and send me a zipped copy of your database, with some
explanations of where to go. My email address without the no.spam part
should work.
Hardcoding columns? Not really. I would hardcode how many days you
could look in the future, but the captions of those columns can be
changed programmatically. Today it starts with "Dec-2" + 6 days;
tomorrow the titles read "Dec-3" + 6 days.

-Tom.
Microsoft Access MVP

I think you are getting closer to my "vision". The only problem I see with
this is that the columns will be "hard coded" and will not actually be equal
to a date. Hard to explain but easy to see. Any way I could send you the
current schedule so you could view it and see what I am actaully up against?
My goal is to make this visually similiar but fast and easy to update and
publish. If you do not have time I understand. Thanks in advance.

Good answers.
I think you are looking for a form with 14 columns, two for each day
(Demand, Scheduled) times 7 days (or whatever number of days is
important to look ahead), plus one column for PartNo, and then the
parts going down for perhaps 250 rows. Perhaps a final column with
some ProjectedOnHand result.
This I would implement with a "temp" table, which is really a
permanent table with (in this example) 16 columns that I would fill
programmatically (Delete query followed by Append query(-ies)) from
the "base" tables. I would probably use Continuous Forms and bind the
form to the table. Then allow any editing needed. Also look at
Conditional Formatting to color certain situations with a background
color.
Then when the user clicks the "Save to Base Tables" button, I would do
just that.

-Tom.
Microsoft Access MVP

Good questions. 1) For the main form for scheduling production, the cell and
cycle time are not necessarialy important, but will be used to evaluate the

[quoted text clipped - 57 lines]

schedule. With ~250 finished good part#s, I need to rely on the system to
easily show me these shortages.

 

[Barrett via AccessMonster.com]

I have this figured out based on your ideas and how our basic structure of
our MRP system is setup. I will set this up like we have discussed and the
"roll" the scheudle weekly making a temp table to safegaurd and mistakes
during the roll. Thanks for your help!

Go ahead and send me a zipped copy of your database, with some
explanations of where to go. My email address without the no.spam part
should work.
Hardcoding columns? Not really. I would hardcode how many days you
could look in the future, but the captions of those columns can be
changed programmatically. Today it starts with "Dec-2" + 6 days;
tomorrow the titles read "Dec-3" + 6 days.

-Tom.
Microsoft Access MVP

I think you are getting closer to my "vision". The only problem I see with
this is that the columns will be "hard coded" and will not actually be equal

[quoted text clipped - 27 lines]