Designing a batch production record database

[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.

 

[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.

 

[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.

 

[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.

 

[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

 

[biofaku]

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

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

 

[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.

 

[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.

 

[biofaku]

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

 

[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!!

 

[Mike Krailo]

It's usually better to start your own thread, rather than digging up and responding to a past one. Last post by biofaku was over two years ago. There were not any examples offered in this thread directly, only a picture of a batch from Pat and here verbal description of it. She did discuss the process on how her app was created, but if this is what you are trying to do, it's a quite a tall order.