Query to Find Unmatched Records Between Two Files

ID: Q114150

The information in this article applies to:

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, 97
Platform          : WINDOWS
Issue type        : kbhowto

Last Reviewed: July 3, 1998