Bills of material

[dgberry]

I am setting up a bill of material tables with parent and child relations. Some of the children could be 3, 4, or 5 levels deep. I need to roll up inventory demands for components in a forecast model, by year of each part needed and the demands for each component. (I really need an ERP system).



I could use some ideas on how to set up the tables, queries to handle this. I do have a start with a table for inventory, a master BOM table, and a BOM child table that works pretty good for entering the data.

Thanks
Dan

[ranman256]

I usually use this layout
BOM
-----
key
descr
parentKey

This is a self-relating table
the 'top' most item has NULL as a parentKey. say key = 5, Wheel, parent=null
then next is
key 6, LugBoltAssembly, parent= 5
then
key 7, Nut, parent= 6

keep moving up (or down) the tree until you run out of data.

[rpeare]

I don't think you need to get crazy with the data entry portion. The real trick is showing the output in a format that is useful to your users.

Here's a simple example of entering your data. It allows you to have multiple assemblies of using the same or similar component parts.

I didn't put a whole lot of time into preventing things like a part can contain it's own part as a component (which you likely would want to prevent)

There are a few ways you can consider showing the component parts.

1. A list of all primary components (components that are not part of any other assemblies)
2. A tree view control showing the levels of parts (this is more complex)
3. A list box with a changing record source so you can go up and down levels of parts

I don't really know what to suggest because I don't really know your business

dgberry.zip

[dgberry]

Based on the BOM's and drawing that I have received, I've created 3 tables. The inventory table with the primary key as the part number, A Master BOM table with a Primary key of AssyPartNumber. Then a child table with a three field primary key (AssyPartNumber, ChildPartNumber and ItemNumber) I then set a one-to-one relation with the masterBom table, AssyPartNumber, and then 2 one-to-many relations with the both the masterBOM table( AssyPartNumber) and ChildBom Table (ChildPartNumber), and a one-to-many relation between the MasterBOM (AssyPartNumber) and ChildBOM Table (AssyPartNumber). I also have "MakeBuy" field and "AssyQty" and "ChildQty" int the respective tables.

I did this to allow multiple levels and some parts being used multiple times on the drawings that I have. (Aerospace parts) For example; an o-ring could be used multile times in an assembly or subassembly. I will need to add up the quantities at each level of these parts, extend the qty needed as appropriate and continue this all the way to the end item. This will then give me a list of items needed with the totals of all qtys needed, multiplied by the need at the particular level.

I thought of making several tables and relating them together for the different levels, but this does not make it dynamic enough for this project.

As I have created a few sub assemblies, at the bottom level, I made the subassembly qty 1 and a make part. When I created it's parent assembly, I set the demand for the subassembly to the qty needed at that level. This was so I could lay down the first pass at one level, then at the next pass set down another level to be a multilplier. I figured several queries could be used with a Visual basic app to control the iterations. Anyway this was my thinking.

[dgberry]

I am using Access 2007.

[rpeare]

the database I attached was 2003 version which you should be able to open, it handles exactly what you posted as your summary, the only thing I don't know is what you mean by 'level'. Let's say you have a 'final' assembly that has a sub assembly, that sub assembly has a sub assembly of it's own, would this be a 3 level assembly? What if you have an 'order' that has two 'final' assemblies, one that is a 3 level assembly, one that is a 4 level assembly where do you report the first level of the 3 level assembly? on the 1st level of the 4 level assembly or on the 2nd level of the 4 level assembly? Regardless of what you do you are going to need to use vba to create the view you want

[orange]

You may get some ideas from this sample hierarchy

[rpeare]

yep that's the tree view I mentioned in my first post (and a great example) though if you have assemblies that are 5 'levels' the tree view will become increasingly slow as you add levels, just be forewarned!

[dgberry]

Showing the tree is not a problem, so far. I am working on a forecast model. I have forcasted demand for enditems for several years. I need to calculate demand of both end items and then their components, by month and year so we can order parts in time to have them by when teir needed. So parts have several year lead times.

So, I can do the time involved forcasting portion. and I have the BOM's setup. My issue is expanding the BOM's out with their associated children, then summing each component in the bom (some items are used multiple times at different levels) into a single component line item with all the demand qty's rolled into it, so I can then compare it to on hand qty's and future needs.. After I get them all rolled up, I know haw to move forward with the time portion of the forcast.

I have thought of using a single child table and do the indenting there, but it didn't seem like a good idea based on a normalized databasse model. I may have to go that route if needed.

But thanks for the Ideas so far, they have given me ideas on how to improve things.

[rpeare]

post a sample database with some garbage data and we can take a look. Without knowing what data structure you settled on it's a bit tricky.

[dgberry]

Talking with you guys and reading, I've come up with a solution for spreading the BOM's to their respective levels. It has taken 7 queries so far, but it is working.

What I've done is I have a field that is "Make\Buy". By using a query to pull out the top levels and then their children, I use the children to filter out just those Items that are make, and then look for the children of those make parts. I add a field called level1, level2, etc... as appropriate as I go. I then use those temp table that are created to then consolidate to a single table. I can then do a sum by part number and group each part into a single record and multiply it as it goes up levels.

Thanks for letting me talk it out here. It really helpped get my thoughts organized.

[orange]

I'm glad that you have something that is working. However, I think rpeare and I would still like to see your tables and relationships and a database with some sample data.
Good luck.

[dgberry]

I am now getting an odd error. I have attached screen shots of my Inventory, MasterBOM and BOM Children table designs, and screen shots of selected data from the Inventory table and the results from the 2 queries that are below. The first, BOMLevel1Q, creates a table called BOMLevel1. I use the results of this to create the next query, BOMLevel2-1Q. I have then attached screen shots of the results from this with the records showing from the inventory table. As can be seen in the results table, part no 50-9717-37 is missing it's 015 item and partno 50-9717-38 has it's 015 record.

I have done repair, various iterations of the linking and parameters on the queries with the same results. I have deleted the records and then recreated them in the tables. The database is only about 7 MEG.

I will add a few more pictures on a second post.

BOMLevel1Q

SELECT MasterBOM.Aircraft, MasterBOM.[Band], MasterBOM.AssyPartNumber AS L1Part, Inventory.NIIN, MasterBOM.FigureNumber AS L1Item, 1 AS L1, MasterBOM.U_M AS L1UM, MasterBOM.Make_Buy AS L1MB, MasterBOM.Qty AS L1Qty, Round([MasterBOM].[Hours],2) AS L1Hrs, MasterBOM.LaborCost AS L1Lbr, MasterBOM.OverheadCost AS L1OH, MasterBOM.LnOTotal AS L1LnOTot, MasterBOM.MaterialCost AS L1Mtrl, MasterBOM.TotalCost AS L1Tot INTO BOMLevel1
FROM Inventory RIGHT JOIN MasterBOM ON Inventory.PartNumber = MasterBOM.AssyPartNumber
WHERE (((MasterBOM.Aircraft) Is Not Null) AND ((MasterBOM.[Band])>="1"))
ORDER BY MasterBOM.Aircraft, MasterBOM.[Band], MasterBOM.AssyPartNumber;

BOMLevel2Q

SELECT DISTINCT BOMLevel1.L1Part, BOMChildren.FigureItemNumber AS L2Item, BOMChildren.ChildPartNumber AS L2Part, BOMLevel1.L1, 2 AS L2, BOMChildren.U_M AS L2UM, BOMChildren.Make_Buy AS L2MB, BOMChildren.Prim_Alt AS L2PA, BOMChildren.ChildQty AS L2Qty, Round([BOMChildren].[Hours],2) AS L2Hrs, BOMChildren.LaborCost AS L2Lbr, BOMChildren.OverheadCost AS L2OH, BOMChildren.LnOTotal AS L2LnOTot, BOMChildren.MaterialCost AS L2Mtrl, BOMChildren.ExtMaterial AS L2XMtrl, BOMChildren.TotalCost AS L2Tot INTO [BOMLevel2-1]
FROM BOMLevel1 LEFT JOIN BOMChildren ON BOMLevel1.L1Part = BOMChildren.AssyPartNumber
WHERE (((BOMChildren.Prim_Alt)<>"A"))
ORDER BY BOMLevel1.L1Part DESC , BOMChildren.FigureItemNumber DESC;

[dgberry]

Here are the rest of the pictures.

[rpeare]

I'm sorry but there is no possible way I'm going to be able to help based on your setup without a sample database you have chosen a route that I would not take and I can not understand it with screenshots alone.