Single Level to Multilevel BOM Conversion

[ac916]

Hi,

I have data that I imported into access (Table1). Basically, the file shows single level BOM (Bill of Material) with BOM Header, BOM Component, Qty per. I need to construct a Multilevel BOM (Table2) base on the single level BOM file, with BOM Level, Finished Good, Subassembly BOM header, BOM component & Qty per.


For example of what I have:
Table1 :SinglelevelBOM
Field 1 – BOM Header
Field 2 - BOM Component
Field 3 - Qty Per
FG1 | SA1 | 1
FG2 | SA2 | 1
FG1 | CO1 | 4
SA1 | SA3 | 2
SA1 | CO2 | 3
SA3 | CO3 | 2
SA2 | SA3 | 4
SA2 | CO4 | 1


What I am trying to get to: Multilevel Build of Materials (BOM)
Table2 : MultilevelBOM
Field 1 – BOM Level
Field 2 - Finished Good
Field 3 - Subassembly BOM Header
Field 4 - BOM Component
Field 5 - Qty Per

1 | FG1 | FG1 | SA1 | 1
2 | FG1 | SA1 | SA3 | 2
3 | FG1 | SA3 | CO3 | 2
2 | FG1 | SA1 | CO2 | 3
1 | FG1 | FG1 | CO1 | 4
1 | FG2 | FG1 | SA2 | 1
2 | FG2 | SA2 | SA3 | 4
3 | FG2 | SA3 | CO3 | 2
2 | FG2 | SA2 | CO4 | 1


Thanks
Andrew

 

[RuralGuy]

Here's a couple of links you might find useful. They are in no particular order.
BOM, with Joe Celko Nested Sets
Bill Of Materials

 

[ac916]

Excellent, thanks for your input.

Excellent, thanks for your input.

I am looking for something with the multi BOM level,


1 | FG1 | FG1 | SA1 | 1
2 | FG1 | SA1 | SA3 | 2
3 | FG1 | SA3 | CO3 | 2
2 | FG1 | SA1 | CO2 | 3
1 | FG1 | FG1 | CO1 | 4
1 | FG2 | FG1 | SA2 | 1
2 | FG2 | SA2 | SA3 | 4
3 | FG2 | SA3 | CO3 | 2
2 | FG2 | SA2 | CO4 | 1


Thanks.