Results 1 to 6 of 6
  1. #1
    robw is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    2

    BOM database - Bill of Material

    This must be a normal use of a database (individual part used in assy's - seems like the classic one to many relationship situation), but I'm not finding any samples/examples.
    I am looking to have a table that self-references - a part can be a component of another part (assy), which in turn can be a component of a higher level assy, which in turn can be a component in a higher level assy. Each product could have a common component in multiple sub-assy's as well as those sub-assy's at different levels of the BOM so not easy to just set up tables for simple parts (which actually have subs which are the raw material), first level sub-assy's, second level sub-assy's, etc. So, I'm left with a single table that has a part and a reference to a higher level part, that relationship being a piece part into an assy, or a sub-assy into a higher level assy or product.


    Can anyone point me to such an example?
    I've tried searching, but BOM is too short a search term, and "Bill Material" (the of also being too short to use for searching) does not seem to find any examples.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sounds like you are talking about recursive functionality

    Access does not have that facility within its sql, but sql server does. With access you achieve the equivalent using VBA to parse through the records.

    If the number of levels is known, or cannot exceed a certain number then you can just join the table to itself for each level (tblBOM in the example below)

    At its simplest you would have two tables - one to detail the product and one to detail the relationship BOM

    tblProducts

    ProductPK
    ProductName
    ...
    ...

    tblBOM
    BOMPK
    ProductFK - links to ProductPK
    ParentFK - links to ProductPK

    ParentFK might be ChildFK depending on which way you want to look at the relationship. The example provided is saying 'this product is a child of these products' which is how you have described it

    If you look in similar threads to the bottom of this thread, I see another thread on this subject

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    That's a recursive relationship and difficult to do in Access. Access queries can't do recursive queries so either you'd have to work with a defined limit of nesting levels of assemblies or write a lot of vba.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I used to work with a CMMS system that contained a BOM component. All the tables were in some type of sql server db and Access was the query tool. AFAIK, none of this was done via recursive methods, rather it was table design. This is another opportunity for me to regret not keeping the tables manual when I retired as it was a 2" thick binder duplex printed list of all the tables and fields info.

    What I'm thinking that made this possible was an equipment location approach that was (perhaps) 7 levels deep, each level a max 4 character code that represented a location. Thus a component on an assembly on an assembly on a system belonging to an area might look like
    (IF YOU READ THIS AND RECOGNIZE THE SYSTEM AND FIGURE OUT WHO I MIGHT BE, KEEP IT TO YOURSELF!)

    KENL.NPSS.TRUC.0165.STER.CYL

    At any rate, my point is that I'm fairly certain this was all done via table relationships but this also required a table for equipment location codes.
    Thus the main "pointer" was e.g. 4568912 not what I typed above. Perhaps that will indicate a possible approach. I admit I haven't looked, but find the claim that you can't find BOM examples surprising because it's such a widely used type of system. Did you look here? http://www.databaseanswers.org/data_models/
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    robw is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    2
    OK. That's what I thought.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    I have a SQL Server database, where a recursive query doing what feels like same thing you want to do.
    As was said here before, recursive queries are possible in SQL Server, not not in Access.
    I had a table struktur imported from ERP system into SQL Server database, and I needed to get BOM list for predefined products into Excel report. The procedure processes table struktur product-wise, and composes a BOM list saved to SQL Server database table.

    The table struktur stores an article st_art_id (a part of some product) and it's parent article st_hart_id, along with structure row and position numbers, and needed quantity st_kvant to produce a single unit of parent article. The product is not defined in ERP program.

    The recursive part of procedure processes a table where products prod_id are defined along with article id (art_id) of those products in ERP system, and saves the BOM list for every product in temporary table dummy_AppStruktur1. For every row of such BOM list, a field prod_pos_nr is calculated. The position of article in BOM list is a string of 2-character 32-bit numbers. '00' - product itself (structure level 0), '01', '02', ... - components added in final stage of production (structure level 1 - usually the product arriving from production, labels, packing materials, etc.), '0101', '0102', ... - components of article (structure level 2) described in position '01', etc. My database allows up to 25 structure levels, and you can have up to 1023 components at any structure level except level 0.

    To calculate 32-bit position numbers, an UDF from special utility dadabase is called. The function returns 2-character (parameter no 3 = 2) Crockford's Base32 (parameter no 2 = 2) string

    There are further calculations made before results are stored in table where BOM lists for every product are stored, but I leave them out for now.

    And here is the part of procedure which does recursive thing:
    Code:
    ...
        TRUNCATE TABLE dbo.dummy_AppStruktur1
    
    
        SELECT @prod_id = MIN(prod_id) FROM dbo.products
    
    
        WHILE @prod_id Is Not Null
    
    
        BEGIN
    
    
            BEGIN TRY
    
    
                ;
                WITH struktur_cte AS 
                    (
                        SELECT 
                            @prod_id AS prod_id,
                                UPPER(CAST(Right('00' + utilities.dbo.udf_NumToNBase32(st_rad,2,2),2) AS varchar(52)))
                            AS prod_pos_nr,
                            @prod_id AS prod_art_id,
                            1 AS st_level,
                            st_id, 
                            st_hart_id,
                            st_rad,
                            st_art_id,
                            st_pos_nr,
                            st_top,
                            st_kvant,
                            st_kvant AS st_kvant_product,
                            Null AS st_kvant_total,
                            st_kvant_extra,
                            st_kvant_inst,
                            st_typ
                        FROM
                            ERPMirror.dbo.struktur
                        WHERE
                            st_hart_id = @prod_id
                        UNION ALL
                        SELECT
                            @prod_id AS prod_id,
                                UPPER(CAST(ecte.prod_pos_nr + Right('00' + utilities.dbo.udf_NumToNBase32(e.st_rad,2,2),2) AS varchar(52)))
                            AS prod_pos_nr,
                            @prod_id AS prod_art_id,
                            ecte.st_level+1 AS st_level,
                            e.st_id, 
                            e.st_hart_id,
                            e.st_rad,
                            e.st_art_id,
                            e.st_pos_nr,
                            e.st_top,
                            e.st_kvant,
                            CAST(ecte.st_kvant_product*e.st_kvant AS numeric(16,6)) AS st_kvant_product,
                            Null AS st_kvant_total,
                            e.st_kvant_extra,
                            e.st_kvant_inst,
                            e.st_typ
                        FROM
                            ERPMirror.dbo.struktur e
                            INNER JOIN struktur_cte ecte ON ecte.st_art_id = e.st_hart_id
                    )
                INSERT INTO dbo.dummy_AppStruktur1
                SELECT 
                    * 
                FROM 
                    struktur_cte  
                ORDER BY 
                    prod_pos_nr option(maxrecursion 10000)
    
    
            END TRY
            BEGIN CATCH
    
    
                INSERT INTO dbo.productFlawedStructur (
                    prod_id,
                    art_id,
                    art_artnr)
                SELECT 
                    prod.prod_id,
                    prod.art_id,
                    prod.art_artnr
                FROM dbo.products prod
                WHERE 
                    prod.art_artnr = @art_artnr 
                    AND 
                    prod.prod_id NOT IN (SELECT prod_id FROM dbo.productFlawedStructur)
    
    
            END CATCH;
    
    
            SELECT @prod_id = MIN(prod_id) 
            FROM dbo.products 
            WHERE prod_id > @prod_id 
    
    
        END
    ...

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. Replies: 13
    Last Post: 01-21-2018, 08:55 PM
  3. Replies: 4
    Last Post: 04-22-2015, 05:46 PM
  4. Material Tracking Database
    By bong in forum Programming
    Replies: 2
    Last Post: 08-19-2012, 07:54 PM
  5. Replies: 1
    Last Post: 05-20-2009, 09:09 AM

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