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

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

The resulting table should display the six records that are in EMP.DBF but are not in ADDR.DBF.

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

The queried data can now be pasted into Microsoft Excel or saved as a query or a table.

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.


KBCategory: kbtool kbusage
KBSubcategory:

Additional reference words: 3.00 4.00 howto



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 12, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.