Automated Production Planner (3 Viewers)

[ChrisMore]

Hi everyone,

I am looking for some guidance on how to approach building an automated production planning system into my existing orders/stock database.

A little background on our business operations and current set up:

• We manufacture fire barrier insulation using various machines and staff to operate each machine
• We currently have a traffic light system which is controlled by the factory managers who use their knowledge and experienced to manually update the capacity of each machine based on production demands
• The traffic light system is built into a work week calendar within the database
• The day in which an order appears in the calendar is controlled by the factory managers entering a date in the Planned Production Date field

Relying on one or two staff to control our current system of course means we have issues when those staff aren't available, so it's important that the system is as automated as possible. I would like the database to calculate the next available Planned Production Date and/or calculate a viable date based on a chosen dispatch date. Ideally, the calendar will include a visual similar to a traffic light system so staff can quickly see the status of each machine. I am thinking this could be in the style of a graph, one for each working day and a line for each machine per graph. The lines can then change from green to red based on the current capacity.

The database currently has data for all the products we manufacture so I think each product will need to be categorized by type and then utilizing a junction table of sorts to list the product types and the factory resources required for each. I know we also need to record time in motion data for each product, but I am otherwise drawing a blank on how to proceed with building my production planning vision.

Any questions, please feel free to ask. I really appreciate any support I can get.

Thanks,
Chris

 

[Gasman]

Well I would be asking the factory managers how they work out capacity/dates and and anything else that can change.

Having that will set the rules on calculating dates, whether we are on green, amber of red, etc.
When you have that information, you should be able to see how to structure the DB to produce that process.

 

[ChrisMore]

Well I would be asking the factory managers how they work out capacity/dates and and anything else that can change.

Having that will set the rules on calculating dates, whether we are on green, amber of red, etc.
When you have that information, you should be able to see how to structure the DB to produce that process.

Hi Gasman, thanks for your input.

This has already been discussed and the managers have a judgement on capacity based on their experience on how many of a product can be manufactured per day. However, this judgement will not be accurate on a daily basis, so we might not be achieving our maximum production capacity. This is why the system needs to be data driven. As we are gathering the data to find this answer, it makes sense to attempt to build an automated planner into our current system using this data. The building part is what I am seeking advice on.

 

[mike60smart]

Hi Gasman, thanks for your input.

This has already been discussed and the managers have a judgement on capacity based on their experience on how many of a product can be manufactured per day. However, this judgement will not be accurate on a daily basis, so we might not be achieving our maximum production capacity. This is why the system needs to be data driven. As we are gathering the data to find this answer, it makes sense to attempt to build an automated planner into our current system using this data. The building part is what I am seeking advice on.

To do that we would need to see your current table structure.
Are you able to upload a zipped copy of the database with no confidential data?

 

[ChrisMore]

To do that we would need to see your current table structure.
Are you able to upload a zipped copy of the database with no confidential data?

the database is split, is it just the back end tables you need to see?

 

[mike60smart]

the database is split, is it just the back end tables you need to see?

Both FE & Be would help

 

[JTrilloJ]

Hi Chris:
I understand that what you need is a "Utility" to calculate machine load.
If so:
1.Do you have a "Route Map" defined for each product in your database?
2. Do you have a setup time and process time defined for each machine and product phase?
3. Do you work against orders or against stock?
There are quite a few more elements to consider, but these are just to give you an idea, in case I can help you with anything once I see your database.
Regards

 

[ChrisMore]

Hi, attached is a sample version of the database. I have only included the calendar section of the database with the traffic light system but you can see the table structure etc.. Double click on the records in the calendar report to open the order form.

@JTrilloJ
1. I'm not sure what you mean by the terminology "route map", maybe you'll get your answer from the sample database.

2. I don't have the data yet for process times. As I said in my initial post:

I think each product will need to be categorized by type and then utilizing a junction table of sorts to list the product types and the factory resources required for each. I know we also need to record time in motion data for each product

As this project is still in the planning stage I don't want to record any data until I know what direction I need to go in for it to work with my database setup. The last thing I want to do is record the data and then find out I need more after starting to build the Production Planner.

3. Again, not sure what you mean by this but you might set the answer from the sample database.

 

[JTrilloJ]

Hi Chris:
Very good work you've done.
Based on the text of your post, what I understood (I'm not sure if it's correct) is that you're ultimately looking for an ERP (Enterprise Resource Planning).
I understood it to be "only" a load of workstations in hours.
For this, each product must obviously be loaded with its route.
An ERP is a very complex topic and I am not sure how to do it (at the computer programming level).
If you add to that the fact that I'm 80 years old and my reflexes aren't those of a young man, you'll understand why I can't make this kind of commitment. If I come up with any ideas on how to translate this idea into VBA Access code, I'll let you know here.
Best regards
PS: I used Google for the translation. If there are any inappropriate terms, I apologize.

 

[GaP42]

Thank you for sharing the sample database.

I have managed to get it to “work” after finding I could not get it to compile and commented out some code that I hope will not adversely affect its operation for this investigation.

Hower, please note that you should (MUST) do some remedial work on your code – every module should have in the header:

Option Compare Database

Option Explicit

Most had the first, none had the second line. You can use options to ensure these appear in every module by default, but you must go back and revisit each existing module. Option Explicit will assist with debugging.

Re your need for production planning/ scheduler. Your fulfilment of an order will come from Stock and/or by manufacturing of the Products.

The production planner is concerned with the production process – not with satisfying the order from stock.

As you indicate each product will utilize one or more machines. There are 328 products in the sample data.

For the product unit of purchase it might be there is a defined period for which a machine is utilised (ignoring set up time) to produce that quantity/unit (and it may be a very small no as the machine rapidly produces output per metre in say 1 sec or less) and a separate one-off set up time for a “job”). CAPACITY

SO MACHINE is a table, PRODUCT is and existing table, the MACHINE_PRODUCT Utilisation table contains the info described above.

An ORDER LINE item will specify a product and a quantity (as a multiple of the product unit of purchase)

For a line item refer to the MACHINE_PRODUCT table to get the projected time needed to produce the product – set up time + quantity*machineproduct time (that may be significantly more complicated but I hope you get the idea: also remember that any one product may involve utilisation of multiple machines.

You may have multiple machines able to take on the same job and you may have multiple machines that must be used sequentially to produce a product.

Having that information you then need to look at scheduling.

A Product should have a known machine utilisation sequence: To produce product A machine 01 is used followed by machine 003, whereas Product B requires Machines 01, 02 and 003 in sequence.

A JOB involves the information from the Machine_Product to look at the availability of the Machine to commence production (and possibly the availability of all the required machines in sequence as a total block) and “reserve” that time

So a Machine_Calendar is needed for each machine. It also needs to incorporate down time scheduled maintenance periods. Perhaps Job set up time, if not part of the Machine_product utilisation table and other periods of unavailability (repairs – unscheduled).

Investigate resource scheduling data bases and how these may then be used to assist that part of the work. Also bear in mind the need for human intervention / confirmation/ oversight of scheduling decisions / suggestions. You may also want to interact with AI for confirmation/assistance as your concept develops

This I hope is a start to getting clarity around your needs.

 

[GaP42]

This may also provide some insights:
batch-production-record-database

A search showed some access templates: at a price, free to explore. Try searching Production Scheduling Database Template
And not access, but the introductory material may give some leads to your solution: https://www.goodfirms.co/production...blog/best-free-production-scheduling-software

 

[GPGeorge]

Hi, attached is a sample version of the database. I have only included the calendar section of the database with the traffic light system but you can see the table structure etc.. Double click on the records in the calendar report to open the order form.

@JTrilloJ
1. I'm not sure what you mean by the terminology "route map", maybe you'll get your answer from the sample database.

2. I don't have the data yet for process times. As I said in my initial post:

As this project is still in the planning stage I don't want to record any data until I know what direction I need to go in for it to work with my database setup. The last thing I want to do is record the data and then find out I need more after starting to build the Production Planner.

3. Again, not sure what you mean by this but you might set the answer from the sample database.

May I suggest that for No 2, you rethink that decision. It's pretty well established practice to have a cycle involving 3 steps for developing anything more than bog-simple database application.

1. Development -- the environment in which you design and create the application
2. Test -- the environment in which you test the performance, reliability and accuracy of the the application -- as it stands at that time.
3. Production -- the environment in which you turn the application over to users for day-to-day work.

You are in phase 1, Development. That means you can, and should, enter data purely for the purpose of figuring out whether the design you are working on is going to do the job you need it to do. That data is not intended for production, nor even for live testing by others. It's purely throw-away data used to validate that the mechanisms being put into place work as intended. Far from being a waste of time or being "lost" when the system goes into production, it's the grist that proves it's a sound design.

You should plan to replace that data both in the testing phase, when you recruit a couple of users to help you by trying it out as close as possible to what their real work entails, and in the production phase, beginning with entirely new data on the day it goes live into production.

In other words, there's no value in handicapping yourself by not entering data purely for the purpose of "trying it to see what happens". Knowing full well that that data is not going to count anyway when you go live.