Query to Find Unmatched Records Between Two Files

Last reviewed: September 2, 1997
Article ID: Q114150
The information in this article applies to:
  • 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.

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." 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.

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


Additional query words: 5.00 1.00 query subtract match unmatched
Keywords : kbprg PgmHowTo kbprg
Version : 1.00
Platform : WINDOWS


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.