netted BOMs query

[Alex]

Hi All,
I’d need to create a complicated query with netted BOMs (Bill Of Materials).
We have:
Item table with major fields: Item No., Prod.Group, Posting Group, and Qty
Item No. - primary key.
Prod.Group – can give to items an indication whether it’s ingredients (BULK,
CHOC, DAIRY, SUG).
Posting Group – giving an indication if item is finished goods (FG), raw
materials (RM), or work in process (WIP).
Items in this table can be FG, RM, WIP, PKG (all together).
Qty – decimal field with ingredient’s qty per FG.

Item No. Prod.Group Posting Group
00622 ss FG
00623 db FG
00655 ss FG
RM123 BULK RM
RM221 SUG RM
RM222 PKG
RC223 WIP
RC225 WIP
…

BOM table:
BOM No. (FG and all BOMs from previous Item table);
Line No.;
Item No. that can be all ingredients, packaging, and BOMs inside FG.

BOM No. Line No. Item No.
00622 1000 RM222
00622 2000 RM122
00622 3000 RM123
00622 4000 RM223
00623 2000 RM222
00623 4000 RM223
RC223 1000 RM222
RC223 3000 RM221
RC225 5000 RM223
…

BOM No. and Line No. is a primary key. So, there are can be many items and
netted BOMs with items and BOMs inside FG.

There is no indication in BOM table whether items from Item No. field are
PKG, WIP (BOM), or RM. All those indications in Item table in Prod.Group and
Posting Group fields.

We’d need to get FG and all ingredients belonged to this FG grouped with
total ingredient’s qty.
If there are more than one the same ingredient in different BOMs per FG they
should be shown as one with total qty. Ingredients can be queried from Item
table it’s BULK, CHOC, DAIRY, SUG from the Prod.Group field.

I’d very much appreciate if anybody could help with this query.

Thanks,

Alex