Query to Find Unmatched Records Between Two Files

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
  • Microsoft Excel 97 for Windows
  • Microsoft Query 97 for Windows

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 dBASE files that ship with Microsoft Excel. The default location for these files is: 

   Version of Excel                      Location
   ------------------------------------------------------------------------
       5.0              WINDOWS\MSAPPS\MSQUERY
       7.0              Program Files\Common Files\Microsoft Shared\MSQuery
       97               Program Files\Microsoft Office\Office
If these files are not installed on your computer, run Microsoft Excel or Office Setup and install Microsoft Query again.

In this example, the source table and the destination table are the two files 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.

To create a Subtract query, follow these steps:

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

    In Excel 5.0 and 7.0:

    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 Customer.dbf file (the source file), and choose Add. Then, select Employee.dbf, click Add, and then click Close.

    In Excel 97:

    Select your dBASE data source in the Choose Data Source dialog box, clear the "Use the Query Wizard to create/edit queries" check box and click OK. Select Orders.dbf, click Add, select Customer.dbf, click Add, then click Close.


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

    In Excel 5.0 and 7.0:

    From the Left box, select Customer.CITY, and from the Right: drop down, 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.

    In Excel 97:

    From the Left box, select Customer.CUSTMR_ID, and from the Right box, select Orders.CUSTMR_ID. Under Join Includes, select the option for "Select ALL Values From 'Customer' and ONLY Records from 'Orders' Where Customer.CUSTMR_ID = Orders.CUSTMR_ID." Click Add, and then click Close.


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

    In Excel 5.0 and 7.0:

    On the Criteria menu, click 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.

    In Excel 97:

    On the Criteria menu, click Add Criteria, and make the following changes:

    • In the Field box, select Orders.CUSTMR_ID.


    • In the Operator box, select Is Null.


    Click Add, and then click Close.


  4. Show the records in the Data pane.

    In Excel 5.0 and 7.0:

    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.

    In Excel 97:

    Double-click the CUSTMR_ID field in the Customer table displayed in the Table pane. This will add the CUSTMR_ID field to the Data pane and display the records where the CUSTMR_ID in the Customer table is not contained in the Orders 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, click the Search button in Help and type:

Join

Additional query words: query subtract match unmatched

Keywords : kbinterop kbdta xlquery
Version : WINDOWS:1.0,2.0,5.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: October 25, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.