XL98: Query to Find Unmatched Records Between Two Tables
ID: Q188235
|
The information in this article applies to:
-
Microsoft Excel 98 Macintosh Edition
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 INFORMATIONOverview 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
- In Microsoft Query, click New on the File menu.
- Select <New Data Source> and click OK.
- In the Create New Data Source dialog box, enter subtract query in the first edit box.
- Select Microsoft 3.01 dBASE PPC in the Select A Driver box.
- Click Connect.
- Locate and select the Sample Databases folder on your hard disk
drive.
- Click Select "Sample Databases."
- Click OK in the Create New Data Source dialog box.
Creating the Query
- Clear the "Use the Query Wizard to create/edit queries" check box,
select the "subtract query" item, and then click OK.
- In the Add Tables dialog box, click CUSTOMER.DBF and click Open.
Click EMPLOYEE.DBF and click Open.
- Click Cancel.
- On the Table menu, click Joins.
- Click the arrow for the Left box and select CUSTOMER.CITY.
- Click the arrow for the Right box and select EMPLOYEE.CITY
- 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
- Click Add. Click Close.
- 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.
- Click Add, and then click Close.
- 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.
REFERENCES
For more information about Joins, click the Office Assistant in Microsoft
Query, type joins, click Search, and then click to view "How joins affect
the result set in a query."
NOTE: If the Assistant is hidden, click the Office Assistant button on the
Standard toolbar. If Microsoft Help is not installed on your computer,
please see the following article in the Microsoft Knowledge Base:
Q179216 OFF98: How to Use the Microsoft Office Installer Program
Additional query words:
XL98
Keywords : kbdta xlquery
Version : MACINTOSH:98
Platform : MACINTOSH
Issue type : kbhowto
|