Microsoft KB Archive/141687

= XL7: How to Find Unmatched Records Between Tables Using DAO =

PSS ID Number: 141687

Article Last Modified on 10/22/2000

-

The information in this article applies to:


 * Microsoft Excel for Windows 95 7.0

-



This article was previously published under Q141687



7.00 WINDOWS kbusage kbhowto





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.

