XL98: Query to Find Unmatched Records Between Two Tables (188235)

MORE INFORMATION

Overview of the Subtract Query Procedure

To perform a Subtract query, create an outer join that joins the two tables, and then add a condition to the query that filters out all of the matching records.

Example of the Subtract Query Procedure

This example uses the sample dBASE tables that ship with Microsoft Office 98 Macintosh Edition. If these files are not installed on your computer, run the Value Pack Installer and select the Data Access component.

In this example, the source table (CUSTOMER.DBF) and the destination table (EMPLOYEE.DBF) are the two tables that you want to join. The destination table is the table that contains the unmatched records. Note that the contents of the source and destination tables are left unchanged.

Creating the Data Source

1. In Microsoft Query, click New on the File menu.
2. Select <New Data Source> and click OK.
3. In the Create New Data Source dialog box, enter subtract query in the first edit box.
4. Select Microsoft 3.01 dBASE PPC in the Select A Driver box.
5. Click Connect.
6. Locate and select the Sample Databases folder on your hard disk drive.
7. Click Select "Sample Databases."
8. Click OK in the Create New Data Source dialog box.

Creating the Query

1. Clear the "Use the Query Wizard to create/edit queries" check box, select the "subtract query" item, and then click OK.
2. In the Add Tables dialog box, click CUSTOMER.DBF and click Open.
   Click EMPLOYEE.DBF and click Open.
3. Click Cancel.
4. On the Table menu, click Joins.
5. Click the arrow for the Left box and select CUSTOMER.CITY.
6. Click the arrow for the Right box and select EMPLOYEE.CITY
7. Choose a join type that specifies ALL records from the destination table and ONLY matching records from the source table.
   
   In this example, select the following option:

   ALL values from 'EMPLOYEE' and ONLY records from 'CUSTOMER' where CUSTOMER.CITY = EMPLOYEE.CITY

8. Click Add. Click Close.
9. Add a criteria field to match on for both tables, and add the condition Is Null.
   
   In this example, on the Criteria menu, click Add Criteria. Make the following changes:
   
   
   • In the Field drop down select CUSTOMER.CITY.
   • In the Operator drop down select Is Null.
10. Click Add, and then click Close.
11. In the Table pane, double-click City in the Employee table to add this field to the Data pane.

The Data pane will contain records that exist only in the destination table, but not in the source table. In this example, the value Redmond should appear in the column labeled CITY in the Data pane. This is the only city listed in the EMPLOYEE table that is not in the CUSTOMER table.

WARNING: Your use or modification of the SQL statement provided in this article is at your own risk. Microsoft provides this SQL statement "as is" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. Microsoft does not support modifications of the SQL statement to suit specific customer requirements.