Joining Two HD Files in MS Query

Open a new workbook and open MS Query.  Select the Customer Balance file (ARPMBAL) and click add (or just double click ARPMBAL in the "Add Tables" dialog box).  Add the Customer Master file (ARPMCUS) as above and click "Close". 

In this case the two file are automatically joined using the Customer Number (ARNO01).  In some cases the files will not auto-join and it is necessary to make the join manually.  This can be done by clicking the field to be used for the join in one file and dragging it to the same field in the second file.  Files can also be joined by clicking Table > Joins.  MS Query uses INNER joins by default.  An INNER join will return only records that are found in both files.

Select Branch Number (ARNO16), Salesman ID (ARID01), Customer Number (ARNO01) and Customer Name (ARNM01) by double clicking the field in the File.

Click Criteria > Add Criteria, click the dropdown and select ARPMCUS.ARFL10 or simply type ARFL10 in the Field box.  Select "does not equal" in the Operator box and type a capital C in the Value box.  Click Add and then Close

Click Records > Sort and select fields by which you want to sort the data.  The order in which the records will be sorted can also be chosen.  Click Add as each field is selected and when complete click Close.

Run the query to display the data that will be returned to the workbook.

Return the data to Excel and format as desired.