Build of Material / BoM

[jlgray0127]

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

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!

[orange]

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 BoM.zip) on his OneDrive. It demonstrates the proper way to create a Bill of Materials relational database application.

Good luck.

[ArviLaanemets]

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, ...
where
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;
etc.
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.

[Info@MsAccess.nl]

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

[kd2017]

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.

[Edgar]

This thread in another forum might be interesting for you:
https://www.access-programmers.co.uk...5#post-1673879

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

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

[dblife]

Could this not be done in one table?

ID (Primary Key)
Item_Description
Cost
Location
Parent_Part_ID

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)

[ArviLaanemets]

... 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: https://www.accessforums.net/showthread.php?t=67768

[dblife]

Hi,
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.

[ArviLaanemets]

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.

[miguelre64]

I tried to go to this link to see an example of a single level BOM creation in access and linkage to Inventory On Hand. If also example of a work order. Trying to construct what I have in excel and move it to access then SQL server for a very small start up business. I tried to go to the Bom link in the reply but nothing was there...

[orange]

Which specific link are you referencing? There are several links in the thread/discussion.

[madpiet]

I tried to go to this link to see an example of a single level BOM creation in access and linkage to Inventory On Hand. If also example of a work order. Trying to construct what I have in excel and move it to access then SQL server for a very small start up business. I tried to go to the Bom link in the reply but nothing was there...

There's an example of a BOM in one of the AdventureWorks database versions. Like 2012.

CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
    @StartProductID [int],
    @CheckDate [datetime]
AS
BEGIN
    SET NOCOUNT ON;


    -- Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 
    -- components of a level 0 assembly, all level 2 components of a level 1 assembly)
    -- The CheckDate eliminates any components that are no longer used in the product on this date.
    WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], 
[ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
    AS (
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.
[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
        FROM [Production].[BillOfMaterials] b
            INNER JOIN [Production].[Product] p 
            ON b.[ComponentID] = p.[ProductID] 
        WHERE b.[ProductAssemblyID] = @StartProductID 
            AND @CheckDate >= b.[StartDate] 
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        UNION ALL
        SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.
[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [BOM_cte] cte
            INNER JOIN [Production].[BillOfMaterials] b 
            ON b.[ProductAssemblyID] = cte.[ComponentID]
            INNER JOIN [Production].[Product] p 
            ON b.[ComponentID] = p.[ProductID] 
        WHERE @CheckDate >= b.[StartDate] 
            AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
        )
    -- Outer select from the CTE
    SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.
[ListPrice], b.[BOMLevel], b.[RecursionLevel]
    FROM [BOM_cte] b
    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.
[ListPrice]
    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
    OPTION (MAXRECURSION 25) 
END;

The hard thing is that it's a kind of gruesome introduction to recursive common table expressions (common table expressions are kind of like views).

The problem is that there are a LOT of differences between Access and SQL Server. I was reasonably proficient at Access when I started learning SQL Server, and it took me a while to get even reasonably good at SQL Server. (I'd ask Albert Kallal about that one.) But if you are open to it's it's definitely worth it.