Part list data base

[FFDiz]

I am trying to create a database to create a purchase list of parts for a final assembly. There are several subassemblies that make the final unit. There are also subassemblies within subassemblies.

I want to be able to create a list of parts for purchase for the completed unit.

I have a table of part numbers that assigns part numbers to parts as ewell as subassemblies. in this table there is field that designates what type of part it is, System, assembly or base part. I am trying to query the "system" to get a quantity of all base parts contained in it. The feils in the table are part number, part name and what type of part it is.

I am having trouble with the queries or what should be in the table.

 

[RuralGuy]

Here's a couple of links that may help:
Bill Of Materials
BOM, with Joe Celko Nested Sets

 

[FFDiz]

Thanks

That helped and I was able to build on it.

However, I would like to change the multiplier in the module to be able to read from the assemly form.

I am not profficient in Visual Basic( I only cut and paste, I am trying to learn but the time is not there).

I am using - Bill Of Materials - that ruralguy attached.

 

[hangglide]

Nested/recursive/BOD problem

Does anyone know how to create the "lft" annd "rgt" fields in the BOM Joe Celko example? I have over 100,000 records in my table.

Example database attached.

http://www.mvps.org/access/queries/qry0023.htm