Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    dgberry is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2015
    Posts
    13

    Bills of material

    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,511
    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.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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

  4. #4
    dgberry is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2015
    Posts
    13
    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.

  5. #5
    dgberry is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2015
    Posts
    13
    I am using Access 2007.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,707

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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!

  9. #9
    dgberry is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2015
    Posts
    13
    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.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

  11. #11
    dgberry is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2015
    Posts
    13
    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.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,707
    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.

  13. #13
    dgberry is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2015
    Posts
    13
    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;

    Click image for larger version. 

Name:	Inventory.jpg 
Views:	37 
Size:	173.7 KB 
ID:	20313Click image for larger version. 

Name:	MasterBOM.jpg 
Views:	35 
Size:	171.9 KB 
ID:	20314Click image for larger version. 

Name:	BOMChildren.jpg 
Views:	34 
Size:	171.5 KB 
ID:	20315Click image for larger version. 

Name:	Table Relations.jpg 
Views:	35 
Size:	245.5 KB 
ID:	20316Click image for larger version. 

Name:	Record1.jpg 
Views:	34 
Size:	215.0 KB 
ID:	20317

  14. #14
    dgberry is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2015
    Posts
    13
    Here are the rest of the pictures.

    Click image for larger version. 

Name:	Record2.jpg 
Views:	36 
Size:	210.9 KB 
ID:	20318Click image for larger version. 

Name:	BOMChildren records.jpg 
Views:	35 
Size:	263.9 KB 
ID:	20319Click image for larger version. 

Name:	MissingRecord.jpg 
Views:	35 
Size:	201.0 KB 
ID:	20320

  15. #15
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Design for Material Amortization
    By gaker10 in forum Database Design
    Replies: 10
    Last Post: 03-09-2015, 10:12 AM
  2. VBA Reference material
    By Paintballlovr in forum Programming
    Replies: 3
    Last Post: 09-07-2013, 08:13 AM
  3. Material Tracking Database
    By bong in forum Programming
    Replies: 2
    Last Post: 08-19-2012, 07:54 PM
  4. Material Receiving Form
    By Solanthus in forum Forms
    Replies: 1
    Last Post: 01-26-2012, 08:25 AM
  5. Bills of Materials / Inventory
    By CRM001 in forum Database Design
    Replies: 4
    Last Post: 05-20-2010, 01:52 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums