Q+E: Query to Return Records That Don't Have Match in 2nd File

• Q+E, versions 3.0, 4.0
• Microsoft Excel for Windows, versions 3.0, 4.0

SUMMARY

In Q+E, you can construct a query that will create a new file containing all the records from one file that do not have matching records in a second file. This type of query is called a subtract query.

MORE INFORMATION

Overview of Subtract Query Procedure

To perform the subtract query, use the Outer Join command to join the two files, then add a condition to the end of the resulting SQL query text. Running this query results in a new file; the contents of the source and destination files are left unchanged.

NOTE: The "Q+E User's Guide" uses the terms "source" and "destination" to refer to the two files being joined. The destination file is the file whose window is active when you choose the Outer Join command. In this procedure, the destination file is the file that contains the unmatched records.

The example below uses the sample files that ship with Microsoft Excel version 4.0. The default location for these files is EXCEL\QE. If these files are not installed on your system, you must run a custom setup and reinstall Q+E.

Example of Subtract Query Procedure

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.

1. In Q+E, open the source and destination files. For this example, open ADDR.DBF and EMP.DBF.

   To Arrange these files side by side, choose the Arrange All command from the Window menu.

2. Select the EMP_ID field in ADDR.DBF to match on, and select the EMP_ID field in EMP.DBF.

3. With the EMP.DBF (the destination file) window active, choose Outer Join from the Select menu.

4. From the Select menu, choose SQL Query.

5. Place the insertion point at the end of the query text and type a space. After the space, type "AND ADDR.EMP_ID IS NULL" (without the quotation marks).

6. Choose OK to run this query.

Note that there are blank fields to the right, beginning with the INTERESTS field. To remove these fields, do either of the following:

• Select a column, then choose Remove Column from the Layout menu. Repeat this procedure for each column you want to remove.

  -or-

• From the Select menu, choose SQL Query, remove the undesired field names from the query text, and then choose OK. (NOTE: The field names appear between the SQL key words SELECT and FROM.)

REFERENCES

"Q+E for Microsoft Excel User's Guide," version 3.0, Chapter 2

For more information about Outer Join, choose Index from the Help menu, and select the Select Commands Menu topic. Select the Outer Join topic.