Summing in Self Join

[LostSon]

I have a self join query that creates a bill of material for 6 levels deep. The fields are:

PartNmbr
Item Class
Level
ChildPartNmbr
QtyReq

The above fields repeat 5 times. I want to sum the "QtyReq" for each parent part number ("PartNmbr") where "Item Class = 43" for each "ChildPartNmbr" on all levels.

The difficulty for me is that the "Item Class" of "43" may be on multiple levels of the bill.

Thanks much!

 

[LostSon]

Here is the SQL for question above, if this will help.

SELECT tblItemMaster.PartNmbr, tblItemMaster.ItemClass, "1" AS [Level 1], tblBOM.ChildClass, tblBOM.ChildPartNmbr, tblBOM.QtyReq, "2" AS [Level 2], tblBOM_2.ChildClass, tblBOM_2.ChildPartNmbr, tblBOM_2.QtyReq, "3" AS [Level 3], tblBOM_3.ChildClass, tblBOM_3.ChildPartNmbr, tblBOM_3.QtyReq, "4" AS [Level 4], tblBOM_4.ChildClass, tblBOM_4.ChildPartNmbr, tblBOM_4.QtyReq, "5" AS [Level 5], tblBOM_5.ChildClass, tblBOM_5.ChildPartNmbr, tblBOM_5.QtyReq, "6" AS [Level 6], tblBOM_6.ChildClass, tblBOM_6.ChildPartNmbr, tblBOM_6.QtyReq
FROM tblItemMaster INNER JOIN (((((tblBOM LEFT JOIN tblBOM AS tblBOM_2 ON tblBOM.ChildPartNmbr = tblBOM_2.PartNmbr) LEFT JOIN tblBOM AS tblBOM_3 ON tblBOM_2.ChildPartNmbr = tblBOM_3.PartNmbr) LEFT JOIN tblBOM AS tblBOM_4 ON tblBOM_3.ChildPartNmbr = tblBOM_4.PartNmbr) LEFT JOIN tblBOM AS tblBOM_5 ON tblBOM_4.ChildPartNmbr = tblBOM_5.PartNmbr) LEFT JOIN tblBOM AS tblBOM_6 ON tblBOM_5.ChildPartNmbr = tblBOM_6.PartNmbr) ON tblItemMaster.PartNmbr = tblBOM.PartNmbr
WHERE (((tblItemMaster.ItemClass)>="01" And (tblItemMaster.ItemClass)<="09"));