Self Join Sum-Group

[divaD]

I have a self join query that pulls a bill of material 7 levels deep. Here is the SQL:
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"));

I want to evaluate the field [ChildClass] for value "43", then sum the [QtyReq] for "43" by [PartNmbr]. The values can be on any level and mixed in with other components.

(Or if there is a better way to explode the BOM, let me know of your suggestions.)

Thank you.

 

[just.a.guy]

I assume you want the "where" conditions ItemClass to still be true.

The simplest way for me to do it is to make a query out of this
complex query. Call it "bom_7_levels".

I would modify the query slightly and add "AS" clauses for each field
to give a unique column name (rather than a qualified name).

For example: "... , tblBOM_2.ChildClass AS ChildClass_2, ... , tblBOM_2.QtyReq AS QtyReq_2....


Then I would create a new query that gets its input from "bom_7_levels".
as follows

SELECT
b7.PartNumber
, sum( iif( b7.ChildClass = 43
, b7.QtyReq
, iif( b7.ChildClass_2 = 43
, b7.QtyReq_2
....
, 0
)
,0
)
) as QtyReq
from bom_7_levels as b7
group by b7.PartNumber.

This assumes that Class 43 only occurs once in the 7 levels.
If it can occur more than once then the "SUM(...)" becomes

, SUM( iif( b7.ChildClass = 43, b7.QtyReq,0)
+ iif( b7.ChildClass_2 = 43, b7.QtyReq_2, 0)
....
) as QtyReq

There is one more case to consider. That is "nulls".
Not all PartNumbers may have 7 levels.
therefore, you need to hand a null value for the
part number.

You do this by wrapping the NZ function around
the ChildClass and QtyReq column names,

as follows: iff(nz(b7.ChildClass,0) = 43, nz(b7.QtyReq,0), 0)

I assume ChildClass is a number if it is a string use: nz(b7.ChildClass,'') = "43"

I did not test this, but I hope it helps.

 

[divaD]

Thanks for your response, just.a.guy.

I will begin work on your suggestions today. It may take a couple of days, but I'll let you know how it goes.

Thanks again.