Please help

I'm trying to join two Parts List that have both common and unique records and print all parts and list qty from the respective source.

Below are the 3 Source Files, each followed by the columns of interest.

Work Order Header File
->Work Order #
->Manufactured Item

Bill Of Material (BOM) File
->Parts List

Work Order Detail File
->Parts List

Below is how I whant the report to show:

ITEM BOM Qty WOQty
Part1 21 21
Part2 14 16
Part3 (Not in file) 5
Part4 21 (Not in file)

This message has been edited. Last edited by: Daren,

Do you have anything in common to join the Work Order File to the other two?

what other fields do the tables have?
For instance: does the work order detail list have the work order # ?

If you post your examples or coding please put between

 [code] 

[/code]
it makes it better readable!

It is easy to join the files.

The Work Order Header file is selected to print/hold only one work order number. It holds the Manufacured Item.

The Bill Of Material file is linked to the Work Order Header file by Manufactured Item. It prints/holds all Parts.

The Work Order Detail file is joined to the Work Order Header file by the Work Order Number. It prints/holds all parts.

My goal is to print a comparison between the Bill Parts and the Parts on the Work Order Parts List. I want to list the part in one row whether it is in the BOM or WO Parts List or Both only once.


I pasted the simplified code below. The problem is it is excluding the items on the WO Detail Parts List that are not on the BOM.

Thanks for the help!

[code]
SET EMPTYREPORT = ON
SET ALL = PASS
TABLE FILE F4801
PRINT
WADOCO
WALITM
WHERE WADOCO EQ &WO;
ON TABLE HOLD AS HWOHEADER FORMAT FOCUS INDEX 'WADOCO'
END

JOIN
HWOHEADER.SEG01.WALITM IN HWOHEADER TO MULTIPLE F3002.F3002.IXKITL IN F3002 AS J0
END

TABLE FILE HWOHEADER
SUM
IXQNTY
BY IXLITM
ON TABLE HOLD AS HBOMQTY FORMAT FOCUS INDEX 'IXLITM'
END

TABLE FILE F3111
SUM
WMTQTY
BY WMCPIL
WHERE WMDOCO EQ '&WO';
ON TABLE HOLD AS HWOPARTS FORMAT FOCUS INDEX 'WMCPIL'
END

JOIN
HBOMQTY.SEG01.IXLITM IN HBOMQTY TO UNIQUE HWOPARTS.SEG01.WMCPIL IN HWOPARTS
AS J2
END
JOIN
HWOPARTS.SEG01.WMCPIT AND HWOPARTS.SEG01.WMDOCO IN HBOMQTY TO MULTIPLE
F553118.F553118.WNITM AND F553118.F553118.WNDOCO IN F553118 AS J1
END

JOIN
HWOPARTS.SEG01.WMCPIT AND HWOPARTS.SEG01.WMMCU IN HBOMQTY TO UNIQUE
F4102.F4102.IBITM AND F4102.F4102.IBMCU IN F4102 AS J3
END

TABLE FILE HBOMQTY
SUM
IXQNTY AS 'BOM,Qty'
WMUORG/P6 AS 'WO,Qty'
BY WMCPIL NOPRINT
END

Use MATCH not JOIN

HOLD OLD-OR-NEW

JG,

It was my first time to use Match and it worked.

Thanks!

Darin please Edit your post and put [Solved] in the Title.

Thanks