XL7: How to Find Unmatched Records Between Tables Using DAO

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:

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

Version           : 7.00
Platform          : WINDOWS

Last Reviewed: March 29, 1997