XL7: How to Find Unmatched Records Between Tables Using DAO

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

   CUSTID
   2003
   2121
   5210
   5563

Sample Query

Sub SubtractQuery()

   '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

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

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

   LEFT JOIN Operation