BOM (Multiply the Qty Require and the Total Qty of export finish goods)

[roystonteo via AccessMonster.com]

Dear Mentors,

I have 2 tables right now.
The Tbl_Bom: Finishgoodpartno, Rawmaterialpartno, QuantityRequire
The Tbl_Delivery: Finishgoodpartno, QuantityOut

I have the query call delivery, this query will total up the quantityOut in
the Tbl_Delivery.

I have the total Out quantity finish goods. How do i multiply the Total Out
Quantity with the Tbl_Bom?
I believe i will have to cross reference the Tbl_Delivery.Finishgoodpartno
and Tbl_Bom.Finishgoodpartno.

What is the query for multiplication?

My Bom only has 2 level, which means all raw material are purchased from
vendors. So i believe i do not need to use nested loops.
Which means:
Finishgoodpartno / Rawmaterialpartno / QuantityRequire
AAA XXXX 1
AAA ZZZZ 2
CCC XXXX 1

Hope somebody can help me on this.

 

[Joshua A. Booker]

Hi Roy,

The query should look something like this:

SELECT tbl_BOM.[RawMaterialPartNo], tbl_BOM.[FinishGoodPartNo],
tbl_Delivery.[QuanitityOut] * tbl_BOM.[QuantityRequire] AS RawQuantityOut
FROM tbl_Delivery INNER JOIN tbl_BOM ON tbl_Delivery.[FinishGoodPartNo] =
tbl_BOM.[FinishGoodPartNo]

HTH,
Josh

 

[roystonteo via AccessMonster.com]

Hi Joshua,

Thanks a lot.
Then what will be the query for the result to be recorded in the
tbl_rawmaterial.outquantity?
I need to minus off the raw material quantity used on the finish goods from
the tbl_rawmaterial in order to get my balance for raw material.

Hi Roy,

The query should look something like this:

SELECT tbl_BOM.[RawMaterialPartNo], tbl_BOM.[FinishGoodPartNo],
tbl_Delivery.[QuanitityOut] * tbl_BOM.[QuantityRequire] AS RawQuantityOut
FROM tbl_Delivery INNER JOIN tbl_BOM ON tbl_Delivery.[FinishGoodPartNo] =
tbl_BOM.[FinishGoodPartNo]

HTH,
Josh

Dear Mentors,

[quoted text clipped - 23 lines]

Hope somebody can help me on this.