Query to Find Unmatched Records Between Two Files

• Microsoft Query for Windows, version 1.0
• Microsoft Excel for Windows, version 5.0
• Microsoft Query for Windows 95, version 2.0
• Microsoft Excel for Windows 95, version 7.0

SUMMARY

This article describes how to create a Subtract query that compares two tables and returns a result set that includes only those records from the first table that do not have matching records in the second table.

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 files that ship with Microsoft Excel 5.0. The default location for these files is WINDOWS\MSAPPS\MSQUERY. If these file are not installed on your system, run Microsoft Excel 5.0 Setup and install Microsoft Query again.

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

1. In Microsoft Query, choose New Query from the File menu. Add the source and destination tables to the query. (The procedure for doing this will depend on the data source).

   For example, in the Select Data Source dialog box, choose Other, select dBASE Files and then choose the Use button. Change the current directory to WINDOWS\MSAPPS\MSQUERY. Select the file CUSTOMER.DBF (the source file), and choose Add. Then, select EMPLOYEE.DBF, choose Add, and then choose Close.

2. From the Table menu, choose Joins. Choose a join type that specifies ALL records from the destination table and ONLY matching records from the source table.

   For example, from the Left drop-down box, select customer.CITY, and from the Right text box, select employee.CITY. Under Join Includes, select option 3 (the Select ALL Values From Employee and ONLY Records from Customer Where customer.CITY = Employee.CITY option). Choose Add, and then choose Close.

3. Add a criteria field to match on for both tables, and add the condition Is Null.

   For example, from the Criteria menu, choose Add Criteria, and make the following changes:

       - In the Field box, select customer.CITY.
   

       - In the Operator box, select Is Null.
   

   Choose Add, and then choose Close.

4. In the Data pane (below the Criteria pane), select the blank column heading, click the drop-down arrow, and select employee.CITY.

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.

REFERENCES

"Microsoft Query User's Guide," version 1.0, Chapter 6, "What Join Lines Are And How They're Created"

For more information about Joins, choose the Search button in Help and type:

   Join