Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2006
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show records with differences between 2 tables (Any Ms Access)

    SELECT * FROM Table1 WHERE (((Exists (SELECT * FROM Table2 WHERE Table1.field1 = Table2.field1 AND Table1.field2 =Table1.field2..............))=False));

    This is the exact syntax to use. Just create a query in Access, right click in the query's window bar and select SQL View. Copy paste the code (with appropriate changes).
    The above code will display records from Table1 if the record exists in Table2 with the possibility that not all its filed values match exactly the values of the relevant record in Table1.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Show records with differences between 2 tables (Any Ms Access)

    Thanks, could be useful.

    Your SQL statement will flag a field that is Null (blank) in both tables as different, since Null = Null doesn't evaluate to True but to Null. So you'll get false positives. The WHERE part should be

    WHERE (Table1.Field1 = Table2.Field1 OR Table1.Field1 Is Null AND Table2.Field1 Is Null) AND (Table1.Field2 =Table2.Field2 OR Table1.Field2 Is Null AND Table2.Field2 Is Null) ...

    This rapidly becomes tedious. Here is a procedure that generates the SQL statement and creates a query with this SQL statement. It requires a reference to the Microsoft DAO 3.6 Object library.

    Sub Compare2Tables(strTable1 As String, strTable2 As String, strQuery As String)
    Dim strWhere As String
    Dim strSQL As String

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strTable1, dbOpenDynaset)

    For Each fld In rst.Fields
    strWhere = strWhere & " AND (<!t>[" & strTable1 & "]<!/t>.<!t>[" & fld.Name & "]<!/t> = <!t>[" & _
    strTable2 & "]<!/t>.<!t>[" & fld.Name & "]<!/t> OR <!t>[" & strTable1 & "]<!/t>.<!t>[" & fld.Name & _
    "]<!/t> Is Null AND <!t>[" & strTable2 & "]<!/t>.<!t>[" & fld.Name & "]<!/t> Is Null)"
    Next fld

    strWhere = Mid(strWhere, 6)

    strSQL = "SELECT * FROM <!t>[" & strTable1 & "]<!/t> WHERE NOT EXISTS " & _
    "(SELECT * FROM <!t>[" & strTable2 & "]<!/t> WHERE " & strWhere & ")"

    On Error Resume Next
    dbs.QueryDefs.Delete strQuery
    On Error GoTo ErrHandler
    dbs.CreateQueryDef strQuery, strSQL

    ExitHandler:
    On Error Resume Next
    Set fld = Nothing
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Use it like this:

    Compare2Tables "tblOrders", "tblOrders2", "qryCompare"

    This will create a query qryCompare that returns records from tblOrders that differ in at least one field from records in tblOrders2.

    Notes:
    1) Chances are that the query cannot be viewed in design view. It can still be viewed in SQL view and in datasheet view.
    2) You can supply the name of a query or SQL statement instead of the name of a table for strTable1 and strTable2, as long as the field names are exactly the same.
    3) For large tables and/or many fields, the resulting query will be s l o o o o o w! See my next reply.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Show records with differences between 2 tables (Any Ms Access)

    Your SQL basically compares every record in Table1 to every record in Table2 to compare them. In many situations, you'll have a unique key on which you can match the tables. You can use this to speed up the query. The version in the attached text file does that; it also displays the fields from both tables side by side, making it easier to spot the differences (but this means it'll only work for tables with fewer than 128 fields). Again, the code needs a reference to the Microsoft DAO 3.6 Object Library.

    Use like this:

    Compare2Tables "tblOrders", "tblOrders2", "pkeyOrderID", "qryCompare"

    This will create a query qryCompare that compares tblOrders and tblOrders2, matched on pkeyOrderID.

    Note: You can supply query names or SQL strings instead of table names.
    Attached Files Attached Files

  4. The Following User Says Thank You to HansV For This Useful Post:

    AOrmsbee (2016-04-05)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •