Suggestions please: Query same table multiple times and UNION results (Create indented BOM)


I have a Bill of Material (BOM) for manufacturing. I am trying to create an indented BOM.

One item may have several components and each component may itself be a manufactured item with its own components.

E.g. In the table below, item1 is a product made up of 1x item2, 1x item3 and 2x item4.
Also, item3 is itself made up of 1x item5 and 1x item6.

Product  Component  Qty
item1     item2            1
item1     item3            1
item1     item4            2
item3     item5            1
item3     item6            1

I am trying to write a query to take a given product, cycle down all levels and produce an "Indented BOM" which will look something like this:

Product  Component  Qty Level
item1     item2            1     1
item1     item3            1     1
item1     item4            2     1
item3     item5            1     2
item3     item6            1     2

In this result table all items used to create the item are displayed including the level at which they occur. I.e. if item5 was itself made up of other items, then they would be listed at Level 3 etc.

Here is what I have come up with but it only goes down one level (to level 2) and am not sure it is giving the correct result anyway.

SELECT q1.*, 1 AS [Level]
FROM tbl_BOM q1
WHERE q1.item = [Product Number] UNION
SELECT q2.*, 2 AS [Level]
FROM tbl_BOM q2
    SELECT *
    FROM tbl_BOM q3
    WHERE q3.item = [Product Number]
  ) q4
  ON q2.item = q4.CompNo

Any ideas welcome, cheers.
LVL 13
ioanePlanning & Analytics ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
try this (I know it is not the best but ;)

Product      Component      Qty
item1      item2      1
item1      item3      1
item1      item4      2
item3      item5      1
item3      item6      1
item5      item7      3
item5      item8      2

result is

Product      Component      Qty      Level
item1      item2      1      1
item1      item3      1      1
item1      item4      2      1
item3      item5      1      2
item3      item6      1      2
item5      item7      3      3
item5      item8      2      3
SELECT b.*, getLevel(product) AS Level
FROM bom AS b;

Public Function getLevel(prd As String)
    Dim strSQL As String
    strSQL = "select * from BOM where Component = '" & prd & "'"
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset(strSQL)
    If rs.EOF Then
        getLevel = 1
        Dim parent As String
        parent = rs("Product").Value
        getLevel = 1 + getLevel(parent)
    End If
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HainKurtSr. System AnalystCommented:

SELECT b.*, getLevel(product) AS [Level]
FROM bom AS b;

since Level is reserved word
I'm sure there is a way to do this in one big SQL statement... but in MS Access there is a less SQL-intensive way:

create one query for each level using the previous level as a helper.  then write the final union query to union them all together:

You would need to add one query for each level and then update the union query as well.

Just run the union query after you're done.
'save this as qry_BOM_Level1:

SELECT tbl_BOM.*, 1 AS [Level], *
WHERE (((tbl_BOM.[Product Number]) Not In (Select distinct item from tbl_BOM)));

'Save this as qry_BOM_Level2:
SELECT tbl_BOM.*, 2 AS [Level]
FROM tbl_BOM INNER JOIN qry_BOM_Level1 ON tbl_BOM.[Product Number]=qry_BOM_Level1.Item
WHERE (((qry_BOM_Level1.Level)=1));

'Save this as qry_BOM_Level3:
SELECT tbl_BOM.*, 3 AS [Level]
FROM tbl_BOM INNER JOIN qry_BOM_Level2 ON tbl_BOM.[Product Number] = qry_BOM_Level2.Item
WHERE (((qry_BOM_Level2.Level)=2));

'Save this as qry_BOM_Union
select * from qry_BOM_Level1
select * from qry_BOM_Level2
select * from qry_BOM_Level3;

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ioanePlanning & Analytics ManagerAuthor Commented:
Thanks, that's exactly the result I'm after but am looking for an SQL-only solution.

I'm really looking for the most efficient solution possible as dealing with a 'lot' of data.

Also, if possible, I would prefer a solution using only table JOINs and UNIONs (ie. no sub-queries, or loops/recursion).

Something like this:

FROM tbl_BOM a
WHERE a.product = [Product Number]
FROM tbl_BOM b
WHERE b.product = (a.component??)

...I know, don't ask for much do I....

Thanks for your input.
ioanePlanning & Analytics ManagerAuthor Commented:
Hi answer_dude,

I am after a single SQL statement but as you say, your solution could be merged.

I do not know how many levels I may need to go, certainly more than 5 so am looking for a way of accounting for this.

HainKurt's idea of using recursion to do this was a good one, but am looking for a clever SQL-only solution.

Thanks for your input.
Alan WarrenApplications DeveloperCommented:
Hi Tramtrak,

you could use an immediate IF function count the existence of product in component and if count returns zero switch the value for 1, else switch it for 2.

Product      Component      Qty      Level
Item1      Item2      1      1
Item1      Item3      1      1
Item1      Item4      2      1
Item3      Item5      1      2
Item3      Item6      1      2


SELECT t1.Product, t1.Component, t1.Qty, IIf((select count(component) from tbl_Bom where Component=[t1].[Product])=0,1,2) AS [Level]
FROM tbl_BOM AS t1;

Open in new window

Alan WarrenApplications DeveloperCommented:
Hi Tramtrak,

Oops, just the read the 'SQL only with NO Sub-Selects" requirement.

Could you post a sample of the data that would result in the desired 5 levels minimum please. Perhaps upload a small db containing the table.


ps... I expect that a sub-select is what the access jet engine does with joins behind the scenes, it converts joins to sub-selects
ioanePlanning & Analytics ManagerAuthor Commented:
Hi alanwarren,

I know I'm asking a lot here.

I will try to mock up a sample table.

Unfortunately cannot simply copy an existing table as data is sensitive but will see what I can do.

I think the difference between using a join and a sub-select is that a sub-select will re-run for each record in the main query whereas a join only runs each query once then joins the results - I could be wrong.

Thanks for your input.
Alan WarrenApplications DeveloperCommented:
Hi Tramtrak,
thanks for that.

I did some performance testing a few years ago using sql 2000, we were testing joins versus sub-selects for some triggers we had, surprisingly the sub-selects were marginally faster, which led me to believe that SQL actually converts joins to sub-selects at run-time, I could be wrong about that assumption.

Alan ";0)
ioanePlanning & Analytics ManagerAuthor Commented:
Ok, did you try using UNION ALL or just UNION?
Alan WarrenApplications DeveloperCommented:
I didn't try any unions of any kind, I have only tried using a sub-select, (derived table), which was successful to ascertain 2nd level items, but I don't have a dataset that would produce a third level at this stage.

ioanePlanning & Analytics ManagerAuthor Commented:
Sorry for the bad house keeping.

I ended up having to use multiple sub selects, so not the solution I was looking for.

Points awarded for participation.

Cheers all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.