Sequencing a Bill of Material

[JAB]

I am trying to create a bill of material for labor time standards. Using a piece of code I found on the web (BOM.zip) I have been successful in assigning the proper level number to each component in the BOM, however the sequence is not correct.

What I get currently is:
0 Car
1 Engine
1 Wheel Assy
2 Pistons
2 Tire
2 Hub Unit
3 Studs
3 Nuts

What I need is:
0 Car
1 Engine
2 Pistons
1 Wheel Assy
2 Tire
2 Wheel
3 Studs
3 Nuts

It appears that one has to step down one path at a time, keeping track of all previous levels because at any given level there may be some items that are sub assemblies and some that are not. Any thoughts or examples on how one would keep track of the sequencing?

Thanks in advance,

 

[Uncle Gizmo]

What I need is:
0 Car
1 Engine
2 Pistons
1 Wheel Assy
2 Tire
2 Wheel
3 Studs
3 Nuts

What defines the order in this sequence ? It's not alphbetical and it's not numeral?

 

[Adrianna]

It appears to be a hierarchy
CAR has an ENGINE
ENGINE has PISTONS
CAR has a WHEEL ASSEMBLY
WHEEL ASSEMBLY has TIRES, WHEEL, STUDS, NUTS

So your tables should be established as such and you can just pass your primary key from the parent table to the child table. This will eliminate having to pass strings for the hierarchy relationship and it will allow you to "group" you data as required. So you can determine the number of levels of materials and make tables for each.

OR

You could create a lookup table with "parts" associated to part "catagoties", then house the details in your Bill of Material table.

tblType
TypeID  TypeName
1        Vehicle
2        Major Assembly
3        Component
4        Parts

tblMaterials
ID   Material            TypeID    ParentMaterial
1    Car                   1            0
2    Engine                2            1
3    Wheel Assembly        2            1
4    Piston                3            3

So you can group by ParentMaterial and sort by TypeID or Material

 

[JAB]

Thank you Adrianna for your response. You are correct in that it is a hierarchy.

Some additional background: I am using three tables; one for assemblies which goes one level deep (the immediate parts necessary for any given assembly), one for components (every part available for use, both assemblies and non-assemblies), and an output table. The code finds the level 1 items assigned to a user-selected level zero item (in my example a car). Any level 1 assemblies are then further decomposed to find what they are derived from (level 2), and so on. The results are placed into the output table for reporting, further processing, etc. The number of levels is dynamic and at any level there can be both assemblies and non-assemblies. Hence my thought that I would have to follow each item beginning at level 1 all the way down to the last level of decomposition before moving to the next level 1 item.

 

[Adrianna]

Wow, why are you outputting into a table and not simply running a query to find the desired information for reporting or form creation? I just ask this because if the data changes and you're constantly having to updated the "output table" or even worse...recreate it...the database may been to be repaired and compacted more frequently then otherwise.

So....my question now is...are the prices/information for the parts static? Or is the "output" table actually your BillOfMaterials containing the individual items and pricing make up?

Reason that I ask it because you need to determine relationships between your tables. The proper structure of these relationships will allow you to "group" there data in queries or reports in the order which you desire.