Manufacturing LOT tracking database design (1 Viewer)

Hi everyone,

Long time lurker of your site. Seems like everyone is really helpful here. I am having trouble figuring out how to design a database which will allow me to track a raw material lot number through a number of productions until it reaches the final product. I then need to be able to look at a final product and identify the original LOT of the raw materials, and to look at the original raw material LOT to identify all finished products produced with that particular raw material.

We have production software right now that doesn't do this very well. It uses an access database but a closed source front-end and the designer isn't into working on it anymore. I have no trouble making a connection from access to our production software's DB and import data into my own database. I designed a database that works really well to track the first and second level. The way we work right now is that the production number generated by our production software is used as the tracking number. My database will import the production order, and then a user fills in the raw material quantities used and the LOT number. This works well when I go from raw material to recipe, but I can't figure out how to keep tracking that recipe into another recipe or a finished product. What I have created right now is driven by the production order.

Here's an example of a raw material and some of the different permutations:

• Raw almonds LOT #12345
  • ALMONDS - Dry roasted almonds with sea salt (recipe component) production #5000
    • TRAILMIX1 - Trail mix (recipe component) production #5001
      • 06811292021 - Packaged trail mix 300g x 12 (finished product) production #5003
    • 06811292022 - Packaged dry roasted almonds with sea salt (finished product) 800g x 12 production # 5002
  • 06811292023 - Raw almonds packaged 500g x 8 (finished product) production #5004

So the LOT number on the final product 06811292021 would be 5003. From that, I should be able to work backwards and see that in 5003, I used the recipe TRAILMIX1 from production 5001, which had dry roasted almonds from production 5000 which used almonds from LOT #12345.

In your example, each item only has one higher-level source item. If that is always the case, consider the adjacency list: for each item, you also store the ID of the parent item, like a hierarchy:

EmployeeID
EmployeeName
BossID

Access is not very good at traversing this stuff if the number of levels is not known in advance but it is doable.

But otherwise, your issue sounds like BOM - bill of materials (where you can have assemblies and subassemblies etc) . Google that

spikepl said:

In your example, each item only has one higher-level source item. If that is always the case, consider the adjacency list: for each item, you also store the ID of the parent item, like a hierarchy:

EmployeeID
EmployeeName
BossID

Access is not very good at traversing this stuff if the number of levels is not known in advance but it is doable.

But otherwise, your issue sounds like BOM - bill of materials (where you can have assemblies and subassemblies etc) . Google that

Click to expand...

My issue is how do I link several employees back to their bosses, and their bosses to their bosses if the employee and their boss are in the same table?

That's kind of what I already ran into.

Looked at your attachment. Not sure what the exact issue is. Can you explain?

In the adjacency model, you link each child to its parent (employee to boss). Many children can be linked to the same parent. This mechanism can store the entire hierarchy.

tblMyHIerarchy
-----------------
EmpID (PK)
EmpName (text)
BossID (FK) - points at EmpID of boss.

In my example, there should be a hierarchical flow like this:

• Raw almonds LOT 5151451
  • Production 041188 transformed raw almonds into dry roasted almonds DRAL4
    • Production 041182 transformed DRAL4 into finished product 23046 - packaged dry roasted almonds

However, I don't know how to make the link between 041182 and 041188. In the case of 5151451, the LOT number represents just a tracking number from the manufacturer. 041188 and 041182 are actual productions. I don't know how to add a previous production to the hierarchy with the way my tables are setup.

You do not link on lot numbers but on the ID like in my example. When you know which lot (5151451) is used to produce the "child" (041182) then you store the ID of LOT 515451 in the record for lot 041182, in a field you do not yet have but could call ParentID.

Same for product/lot 23046 -in its record store the ID of lot 044182.

I gotta run now, but will take a peak much later - meanwhile, if still stuck, say so, and surely somebody else will come along and help you out.

Hmm. I think I got it now. Thanks so much for your help!

duplicate post here

http://www.access-programmers.co.uk/forums/showthread.php?t=277743

??? your link goes back to this post?

sorry, copy and pasted the wrong link

http://www.accessforums.net/databas...anufacturing-lot-tracking-database-52641.html

You need to read this http://www.excelguru.ca/content.php?184 and show some contrition and do the appropriate!!! or else it's the doghouse for you.