How to recursively query a nested Assembly Item Table.

[Woo Mun Foong]

Hi there,

I have 2 tables.
Table 1 - tblAssembly is an table that contains AssemblyID and Component ID.
AssemblyID can be nested inside another AssemblyID
tblAssembly has the following data :-

AssemblyID ProductID
================
ASSM01 Item01
ASSM01 Item02
ASSM02 ASSM01
ASSM02 Item02
ASSM03 ASSM01
ASSM03 ASSM02

Table 2 - tblOrderDetail
OrderID LineItemID
=============
001 ASSM01
001 ASSM02
001 ITEM01

where LineItemID is actually AssemblyID in tblAssembly.

How can I create a queries that produce the following results?
OrderID LineItemID ProductID
====================
001 ASSM01 ITEM01
001 ASSM01 ITEM02
001 ASSM02 ITEM01
001 ASSM02 ITEM02
001 ASSM02 ITEM02
001 ITEM01 ITEM01

Line 1-2 is the reault of exploding ASSM01 to get ITEM01, ITEM02
Line 3-5 is the reault of exploding ASSM02 to get ASSM01, ITEM02 where
ASSM01 need to be explode further to get ITEM01 and ITEM02. Resulting in
getting 3 rows in return, namelt ITEM01,ITEM02, ITEM02
Line 6 is a not a Assembly Item and need not do futher exploding.

Thanks In Advance.

 

[Allen Browne]

See:
Bill Of Materials
at:
http://www.mvps.org/access/modules/mdl0027.htm
The link includes a sample database.

If you want something more complex, try some of the articles by Joe Celko:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html