Results 1 to 10 of 10
  1. #1
    jlgray0127 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Central Illinois

    Build of Material / BoM

    I have a table which contains our Build of Material data. The data is set up with the following columns:


    Our BoM Structures can have up to 10 levels.

    I am trying to find a way to put together the BoM Structure in a query. I have not found many options for this, and am completely stumped with anything I have found.
    I believe I have to figure a way to loop the query to run at least 10 times to pull all the potential components that would roll up through the BoM.

    For instance, a parent item is made up of 10 components. Those 10 components could be made up of more components, and then on and on, up to 10 items.

    Anyone have any experience with creating a multiple level Build of Material before and possibly point me in the right direction on how to attack this?

    Hopefully this is a general enough description. Please let me know if I need to be more clear in my description and as always, thank you for any assistance you can provide!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Ottawa, Ontario, Canada; West Palm Beach FL
    There may be some useful info in the Similar Threads at the bottom of this page.
    Bill of Materials is described quite well here to assist with concepts and structure.

    Also, Ken Sheridan has a demo relational database application called BoM (in on his OneDrive. It demonstrates the proper way to create a Bill of Materials relational database application.

    Good luck.

  3. #3
    Join Date
    Apr 2017
    Access isn't best tool for this. For such tasks, you need to use recursive queries, which aren't supported in Access.

    Recursive queries are supported in SQL Server.

    I have worked work with such structures using SQL Sever BE (which read product structures from 3rd-party ERP program), and Access FE (or Excel report file). SQL Server converted the product structure like you described into SQL Sever table like:
    tblProdStru: ProdStruID, ProductID, StructureElementNo, ParentComponentID, ComponentID, ComponentQty, ...
    ProdStruID is autonumeric PK;
    ProductID is ID of article, for which the structure is created;
    ParentComponentID is ID of article from previous structure level, the current component belongs into (for 1st component level it will be ProductID;
    ComponentID is ID of article, which is used as component of product in current structure level;
    ComponentQty is the quantity of current component in single unit of parent component;
    There may be any additional fields for info you may need;
    The field StructureElementNo is field which allows Access (or Excel) to work with this table. It is a string which determines the position of all components in product. I used string, where for every level of structure, 2-character part of StructureElementNo was used. At first, I tried with 2 numbers with every structure level (up to 99 different components per parent component), but eventually I had to use 32-bit numbers instead (with 2 characters long 32-bit number per level you can have 1023 different components for every parent component).
    StructureElementNo = "00" represented the product;
    StructureElementNo = "01" represented the 1st component on 1st structure level;
    StructureElementNo = "0101" represented the 1st component of 1st parent component on 2nd structure level;
    StructureElementNo = "02" represented the 2nd component on 1st structure level;
    When displayed, the structure for product must be ordered by StructureElementNo.

    There was a procedure, which cycled through a list of products, and for every product, the procedure called a recursive query to create a structure for this product.

  4. #4 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2022

    I have done some research for a client

    If you have a db with the all the components of 1 product/part. I can implement my code to see if it works.

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    If you decided you want to explore learning recursive queries SQLite also has this feature.

    As Arvil suggested using SQL Server as a back end, you could do the same with a SQLite database file.

    Fair warning: there will be a bit of a learning curve with recursive queries.

  6. #6
    Edgar is online now Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    This thread in another forum might be interesting for you:

    Have you tried creating a nested dictionary based on a self referencing table with fields
    id_fk (referencing id_pk)

    Then, using vba, first append children to parents, then append parents to other parents.

  7. #7
    dblife's Avatar
    dblife is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2011
    South Shields, UK
    Could this not be done in one table?

    ID (Primary Key)

    The table references itself.
    Top level parent parts have no parent_part_id but every sub part has a parent_part_id, whether it is a 2nd level part, 27th level part etc.
    You wouldn't be limited to 10 levels either.
    When you create the query to choose what belong to which parent, simply add the same table again and enforce referential integrity (to make sure you force a genuine ID for the parent part)

  8. #8
    Join Date
    Apr 2017
    Quote Originally Posted by dblife View Post
    ... When you create the query to choose what belong to which parent, simply add the same table again and enforce referential integrity (to make sure you force a genuine ID for the parent part)
    This will be a recursive query, which aren't supported in Access.
    Or you have to write this query specifically for this product only - determining in query every row of result table by code.

    In databases, which support recursive queries (e.g. SQL Server), you can run this for single product only (and show the gotten structure in form or in report). To get structures of many databases as single step operation, you have to run a stored procedure, which runs a cycle to run recursive query for product, and to write the results into table.

    An example of recursive query I have used:

  9. #9
    dblife's Avatar
    dblife is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2011
    South Shields, UK
    I was hoping to explain my method in the attached db.
    The items in the inventory reference each other and when the user asks for a part ID they get a report with the child items required.
    Please see attached.
    I have included basic data for two large assemblies, a car and a house.
    The third assembly is the wheel which goes on the car but you could keep going to extra levels.
    The attached database shows what I mean.
    When you double-click the report it will ask you for the item you want to make, entering 1,2 or 7 (Car house and wheel) gives you the BoM for each assembly.

    Click image for larger version. 

Name:	099.jpg 
Views:	15 
Size:	52.6 KB 
ID:	50081 Click image for larger version. 

Name:	098.jpg 
Views:	15 
Size:	101.6 KB 
ID:	50082 Click image for larger version. 

Name:	097.jpg 
Views:	14 
Size:	47.1 KB 
ID:	50083
    Attached Files Attached Files

  10. #10
    Join Date
    Apr 2017
    This will work only, when every component goes into a single product only, and only once! When e.g. the brick may be used to produce a garage, a bridge, a living house, a shop building, etc., then you will have for this same brick in structure table a lot of records with different parent id's. Even more, you may in building from bricks have some other component (e.g. offen), which is build using same bricks too, only not on 1st structure level anymore - with offen as parent element, and offen having building id as parent id.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Bill of Material Help
    By TheLostDrafter in forum Access
    Replies: 10
    Last Post: 10-12-2023, 06:09 AM
  2. Sum of reserved material - day-by-day
    By TheMaramatanga in forum Queries
    Replies: 9
    Last Post: 05-15-2021, 02:27 AM
  3. Bills of material
    By dgberry in forum Database Design
    Replies: 16
    Last Post: 04-14-2015, 01:21 PM
  4. Design for Material Amortization
    By gaker10 in forum Database Design
    Replies: 10
    Last Post: 03-09-2015, 10:12 AM
  5. VBA Reference material
    By Paintballlovr in forum Programming
    Replies: 3
    Last Post: 09-07-2013, 08:13 AM

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