Exploding a Bill of Material (BOM)

  • Thread starter Thread starter VJ
  • Start date Start date
V

VJ

Folks,

I have the following 2 tables with the following data:-

1) BOMProduction
BomID, Item
bom-table, Table,1
bom-leg, Leg,1
bom-top, top,1

2) BomConsumption
BomID, Item, Qtyper
bom-table, Leg, 4
bom-table, top, 1
bom-leg, Wood,1
bom-top, Wood,1

As the name suggests, BOMProduction suggests the item being produced by the
BOMid and BOMConsumption tells what item is being consumed. So for ex: to
make a 'table', I consume 4 legs and 1 top.

Question:
---------

Is there any single SQL statement which takes the item being produced, an
iteratively loops to tell me the entire consumption pattern.. ie Table ->
Legs, Top -> Wood
ex: If I select 'Table', it should take the BOMId assciated with table ie
'bom-table' from BOMProduction. Now you search for this bomid in
bomConsumption and fetch the items 'Leg' and 'Top'. Now you find the
associated bomid for TOP in BOMProduction ie bom-top and search for consumed
item in bomconsumption in al oop. The exit criteria would be when the last
item found in BOMComsumption returns a NULL in BOMProduction ex: Wood

I know thre is a guru out there to help me with this statement :-)

TIA,
Vijay
 
Folks,

I have the following 2 tables with the following data:-

1) BOMProduction
BomID, Item
bom-table, Table,1
bom-leg, Leg,1
bom-top, top,1

2) BomConsumption
BomID, Item, Qtyper
bom-table, Leg, 4
bom-table, top, 1
bom-leg, Wood,1
bom-top, Wood,1

As the name suggests, BOMProduction suggests the item being produced by the
BOMid and BOMConsumption tells what item is being consumed. So for ex: to
make a 'table', I consume 4 legs and 1 top.

Question:
---------

Is there any single SQL statement which takes the item being produced, an
iteratively loops to tell me the entire consumption pattern.. ie Table ->
Legs, Top -> Wood
Click to expand...

Yes, but only if there is a maximum number of levels deep it could go that
does not exceed the limits of the Access query compiler. You can just build a
multi-level self-join with a different alias for each level. Note that you
can also make a table its own subdatasheet, and drill down in datasheet view,
though that is not very robust in an application. You can do a similar thing
with nested subforms in datasheet view, but I think the maximum nesting is 11
levels deep.

As for the single SQL statement, it might look something like this...

SELECT level1.name as name1, level1.name as name2, level3.name as name3,
level4.name as name4, level5.name as name5
FROM (((tblParts level1 INNER JOIN tblParts level2 ON level1.id =
level2.parentID) INNER JOIN tblParts level3 ON level2.id = level3.parentID)
INNER JOIN tblParts level3 ON level3.id = level4.parentID) INNER JOIN tblParts
level5 ON level4.id = level5.parentID
ORDER BY level1.name, level1.name, level3.name, level4.name, level5.name

A given BOM application might use several different approaches for different
purposes in the same system.
 
Back
Top