XL7: How to Find Unmatched Records Between Tables Using DAO

Last reviewed: March 27, 1997
Article ID: Q141687
7.00 WINDOWS kbusage kbhowto

The information in this article applies to:

  • Microsoft Excel for Windows 95, version 7.0

SUMMARY

In Microsoft Excel, you can use DAO (Data Access Objects) to manipulate data in external databases. This article describes how to create a Subtract query to compare two tables and return only those records from the first table that do not have matching records in the second table.

MORE INFORMATION

Microsoft provides examples of Visual Basic for applications procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

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.

The following macro example demonstrates how to create a query on two joined tables that subtracts the matching records, returning only those records that do not match on the joined field. In this example, two tables are used for the join--a table named Sales95.dbf and another named Sales94.dbf. The tables are joined on the field CustID.

Sales94.dbf             Sales95.dbf
----------------        ---------------
CUSTID  ORDER           CUSTID  ORDER
1000    84.89           1234    59.53
1001    35.87           1234    90.09
1234    60.87           1987    85.65
1234    50.45           2003    91.08
1245    47.23           2009    53.72
1265    84.95           2121    50.22
1987     0.77           5210    30.46
2009     0.47           5563    23.63
                        5563    56.36

The subtract query returns all of the customer ids (CustID) that are in the Sales95 table but not in the Sales94 table. The results of the query would be the following:

   CUSTID
   2003
   2121
   5210
   5563

Sample Query

Sub SubtractQuery()

Dim Db As Database Dim Rs As Recordset Dim SQL As String Dim i As Integer

   'Create the SQL Statement for the Subtract query
   SQL = "SELECT DISTINCT Sales95.CustID FROM Sales94, Sales95, " & _
      "Sales95 LEFT JOIN Sales94 ON Sales95.CustID = Sales94.CustID " & _
      "WHERE ((Sales94.CustID Is Null))"

   'Open the database
   Set Db = OpenDatabase("c:\my documents", False, False, "dbase IV;")

   'Run the query
   Set Rs = Db.OpenRecordset(SQL)

   'Return the Column Headers to Sheet1
   For i = 0 To Rs.Fields.Count - 1
      Sheets("Sheet1").Range("a1").Offset(, i) = Rs.Fields(i).Name
   Next

   'Return the recordset to Sheet1
   Sheets("Sheet1").Range("a2").CopyFromRecordset Rs

   Db.Close

End Sub

To use the Data Access Object from a Microsoft Excel macro, use the following steps:

  1. Activate a module sheet in the workbook containing the macro.

  2. On the Tools menu, click References.

  3. Select the Microsoft DAO 3.0 Object Library check box, and then click OK.

REFERENCES

For more information about Data Access Objects, click the Index tab in Microsoft Excel Help, type the following text

   DAO, Overview

and then double-click the selected text to go to the "Data Access Objects Overview" topic.

For more information about the LEFT JOIN operation, click the Index tab in Microsoft Excel Help, type the following text

   LEFT JOIN Operation

and then double-click the selected text to go to the "LEFT JOIN, RIGHT JOIN Operations (SQL)" topic.


KBCategory: kbusage kbhowto
KBSubcategory:

Additional reference words: 7.00 joins joining matching
Keywords : kbhowto kbusage
Version : 7.00
Platform : WINDOWS


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: March 27, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.