Determining if 2 tables have differences 1

[Waraq]

Hi All.

I am hoping that you can help. I have 2 Link Tables that to bring in 2 files that I need to see if there are differences. These files have about 12K lines with about 40 columns.

One of the files is generated from our production environment and one is generated from our QA environment. We compare the output to make sure that the QA environment does indeed give us the same results as production.

I want to compare the 2 files based on a multiple key and determine if there are differences between the 2 files.

Hope people can lend me some insite.

Thanks.

Waraq

 

[Golom]

Are you looking to compare all 40 columns for differences or just some subset of them such as the key columns? Are two records "different" if they have the same keys but differences in non-key values?

 

[p27br]

can't you use the 'find unmatched' query wizard ?

 

[Waraq]

I am looking to compare all colums - turns out to be 53 or so - and basically all the data in all the fields should match up.

Thanks.

-c

 

[Waraq]

The "Find Unmatched" Query Wizard, doesn't seem to work.

 

[Golom]

First we must assume that any two tables that you want to test for differences have exactly the same fields. If that's not the case then the comparison becomes a manual one of considerable complexity. There are really 3 problems here

[li]Records that appear in table1 but not in table2.[/li]
[li]Records that appear in table2 but not in table1.[/li]
[li]Records that appear in both tables where "appear" means that some key field or fields match, but the values in other fields are different.[/li]

For the first 2

Records in tbl1 but not in tbl2
[blue][tt]
Select tbl1.*
From tbl1 LEFT JOIN tbl2 ON tbl1.f1 = tbl2.f1
Where tbl2.f1 IS NULL
[/tt][/blue]

Records in tbl2 but not in tbl1
[blue][tt]
Select tbl2.*
From tbl1 RIGHT JOIN tbl2 ON tbl1.f1 = tbl2.f1
Where tbl1.f1 IS NULL
[/tt][/blue]


Records with Different Field Values

Then Write Some Code
[blue][tt]
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim acn As New ADODB.Connection
Dim st1 As String
Dim st2 As String

' ... code to open the connection ...

rs1.Open "Select tb1.* From tb1 INNER JOIN tb2 ON tb1.f1 = tb2.f1 " & _
"Order By f1, f2, f3, f4, ...", acn, ... other parms ...

rs2.Open "Select tb1.* From tb1 INNER JOIN tb2 ON tb1.f1 = tb2.f1 " * _
"Order By f1, f2, f3, f4, ...", acn, ... other parms ...

Do Until rs1.EOF
if rs1.GetString(adClipString, 1, ";") <> _
rs2.GetString(adClipString, 1, ";") Then
Debug.Print "Unmatched"
Debug.Print "Table tb1: ", rs1.GetString(adClipString, 1, ";")
Debug.Print "Table tb2: ", rs2.GetString(adClipString, 1, ";")
End If
rs1.MoveNext
rs2.MoveNext
Loop
[/tt][/blue]
Note that corresponding fields in the two tables must be in the same left-to-right order for this to work.