Designing a batch production record database (1 Viewer)

[biofaku]

I'm sailing on a travel that I think I'm not experienced enough to do, so I came here expecting some guidance because after a lot of research on internet, came with few ideas.
I have been tasked with developing a database to track the production history of different items. The main problem is, every item is manufactured differently, every one have different ways to be created, and the parameters that are checked vary a lot from one to another. To rough it up a little more, the recipe may change over time, so with that can change the parameters that one used to follow, that means adding new ones or taking out others.
The idea would be to be able to track the batch number, the production order, which item is being produced and all the variables of the quality control, between other things related to the machinery used on the process.
At first I thought that I should do a EAV db, but I discarded it, later I found some examples of 6NF that could apply, but I'm not sure if its fully applicable to the working scheme its implemented here. I would not like to, but making a table for every single product may end be the only way to work this, but I don't like that much that Idea either.

Some one of you have worked with something similar to this? If no, still any idea how to give a shape to this?

Thanks in advance.

 

[Pat Hartman]

I created an application for Clairol that managed the manufacture of various hair care products such as shampoo and dye. There was a product table, a formula table, and an instructions table. The components were entered as a percentage so that the batch size was multiplied by the percentage to get the actual quantity of any particular material in the formula for a batch. Dye packs and colors were such minute quantities that they were handled separately. The instructions table specified the steps to take to build the batch and included things such as temperature, mixing speed, and mixing time and when to reset the scale. Every evening, the application read a production schedule and printed out batch instructions for the next day for each work station. They were also available on line if the operator preferred to use the computer to view the instructions. He was required to acknowledge each step. Some of the machines were automated and the app created files that the machines could read to do their thing. For simplicity, most materials and formulas were done by weight. Only a few were done using volume measurements. The instructions table had a FK to the formula table so that the quantities could be put in the right place in the instructions.

Is that what you are trying to do? Here''s a picture of the printed instructions.

 

[biofaku]

It's mostly something like that, on a related Industry also (pharma). Here they are not giving so much thought to the ingredients (for now at least, I already see them coming asking for that too!), but for now they are looking more to each step on the process and all the recorded variables. So eventually, you could check, how fast was a mill working, or in which room was done certain step to say some examples.

 

[Pat Hartman]

Everything we made with this application was liquid. I suppose if the end result were pills, the final step would be extrusion or filling molds. Does this give you a place to start? Also, I edited my first reply to show a sample.

 

[biofaku]

I think you have sparked something for me with that image you added.
Just to be curious, the "actual" column was to be filled manually (on pen) or it could be completed on access too? What I'm looking is to track for every batch of the different products those values, but have in mind that for some processes the recipe part could be half of that while on other processes could be twice that long.

 

[Pat Hartman]

In the beginning it was filled in with pen because that was how they had always operated. One of the first enhancements to the app was to produce a similar form where they could enter it on line but first the work stations needed to be equipped with computers. So, it was both ways for a while.

 

[biofaku]

Oh, its great then, now I think it's possible to do what I was trying, even if the manufacturing process do vary a lot from one product to another. Thanks Pat, your help it has been really useful.

 

[Pat Hartman]

Glad this helped. It was done a very long time ago and was probably the first major database I ever built. Something that runs the shop floor is pretty major

I doubt that I have a working copy of the db. It's been a lot of computers between then and now and somehow ancillary files go missing but post back if you have other questions. I was amazed that I could actually bring up a picture of what we printed.

 

[biofaku]

Oh, if you had a sample without data, just the basic layout would be fantastic, Im somewhat puzzled and wanted to see how you tracked the actual columns of that batch card.

Its like the lack of a solid work structure that I have found here it's what it's making it harder to define all the relationships.

Thanks again for everything

 

[Pat Hartman]

The columns are tied to steps. If a step needs material, it has a FK to the material in the formula to pick up the percentage. The percentage is multiplied by the size of the batch. The table looks like the picture up to the Theoretical section. The other sections are data input for each batch so they are in a different table. So, half the report is the "definition" which is static text + a calculated amount. The other half is actuals that are typed in as the batch is produced. So, the detail section comes from a join of Four tables.
Instructions, Formula, Batch, Actuals. The first and second are static. The third is the header for the batch which includes the batch size. The fourth is a child of the Batch table and includes a row for each row in instructions which contains the columns that are manually filled in as the batch is produced.

Talking about this project is bringing back vivid memories. Our work space was an enclosed mezzanine above the shop floor so we had to walk along the edge of the manufacturing floor to get to our stairs. The smell of Herbal Essence (that was the primary product of the plant) still makes me nauseous and then there were the days where we had to wait outside while they cleaned up some vile smelling chemical spill

 

[biofaku]

Oh, I guess you are having quite a trip over the memory lane

Thanks for the tips, it's really helping me!

 

[Pat Hartman]

I thought of another thing you should know.

When the Batch record is created, the app automatically adds empty records for the actual steps table.

Another thing was a feature I added later that allowed me to modify the strings that made up the instructions. This is a little fuzzy so I'll do my best. Sometimes, the instruction text needed an embedded variable. So, we defined a bunch of variables that they might use. and gave them names like ^StationNum^ and as the document was being printed, there was a function that searched each instruction string and substituted some value for a token. I think we used the carrot because that would never normally occur in their instructions. The variables were defined on the Batch table/form. The values were not hardcoded although many of them had defaults.

The users couldn't add tokens on the fly. Since they were stored ad columns in the Batch table, if we ever found we needed a new one, the table and form needed to be modified but in the three years I was there, we never actually had to add a new token.

 

[biofaku]

I'm glad you said that the app added empty records for the steps, because that is what I was thinking you made. It's the only doable approach I found. Although is gonna be a lot of coding to let the app know when add an specific set of steps for a batch.

Now I'm kinda stuck with a prior part that maybe you can help me sort.
The batch number for a process (like filling a shampoo bottle) may be different from the one present at the box.
When they got an order production, the manufacturing process it's not always lineal. What I mean, sometimes they store some part of the batch for later and they only pack the rest for distribution. That means that later, a batch picks the stored product and packs it, with a new batch number.
But other times, they pack everything that has been manufactured, and this duality is somewhat hard to put it on tables, at least to me.

 

[Pat Hartman]

It's not a lot of work at all. It is simply an append query that you run in the AfterUpdate event for the new batch record. Just check to make sure there are no records there and then run the append query.

That's like the concept of a kit in a BOM. At the moment, the only thing that comes to mind is to make the partial batch a component in the formula. I'd have to know a lot more about the process to solve this with any finesse.

 

[biofaku]

Yes, the process it's really complex to explain it, only by analyzing it a lot I'm starting to come with a relational map, but it's taking more effort than I thought it could.

 

[biofaku]

Hey Pat, it's me again. Just wondering something, if you with the batch scheme you shared you had wanted to make a comparison of the performance for each step between all the batches, how would you do it?

I was trying to do something like that, filtering through the Process FK and the Step FK to have "column" of data, but there might be a better way.

 

[Pat Hartman]

A cross tab query might do what you want.

 

[biofaku]

Oh yes! That worked perfectly. Never thought that!
Thanks Pat!

 

[Pat Hartman]

You're welcome

 

[Emmanuel Manu]

Hi !! I just found your thread and I'm exactly at the same spot you have been biofaku! I was wondering if you made it and maybe share some informations or an example file? Totally new to Access but good knowledge in Excel, I hope I will catch up quickly!!