comparing two tables and via vba

[webisti]

Not to make the post too long I did pasted only table B, but it is exactly the same structure.
There are no relationships between the two.


Usually I do use the Field MATNR as a key field for both tables, for it should be the same number while the rest might change on table A,

[orange]

What exactly do you want to check?

TableA Field1 vs TableBField1
TableA Field2 vs TableBField2
TableA Field2 vs TableBField2


Can you post a copy of your db and table with just 1 or 2 records? I'd like to see datatypes and sizes etc.

[webisti]

actually is
TableBField1 versus TableAField1
TableBField2 versus TableAField2
TableBField3 versus TableAField3
TableBField4 versus TableAField4
TableBField5 versus TableAField5 and so on
TableBField44 versus TableAField44

all fields are text value, no numbers or dates. just text.

[orange]

I'll look at a solution and get back.

[orange]

Here is a procedure that will compare 2 tables. It will compare table1 field1 with table2 field1...
Output shows the record number, the keyfield, field name and value of tableA and tableB in immediate window

'---------------------------------------------------------------------------------------
' Procedure : Compare2TableFieldByField
' Author    : Jack
' Date      : 17/03/2014
' Purpose   : Compare 2 tables field by field. Display non matches in the immediate window.
'
'        Output shows the record number, the keyfield, field name and value of tableA and tableB
'---------------------------------------------------------------------------------------
'
Sub Compare2TableFieldByField(tblAName As String, tblBName As String, iPosKeyFld As Integer)
          Dim db As dao.Database
          Dim i As Integer
          Dim J As Integer
          Dim K As Integer
          Dim RecNo As Integer
          Dim rsA As dao.Recordset
          Dim rsB As dao.Recordset
          Dim strA As String
          Dim strB As String
10        On Error GoTo Compare2TableFieldByField_Error

20        Set db = CurrentDb
30        Set rsA = db.OpenRecordset(tblAName, dbOpenSnapshot)
40        Set rsB = db.OpenRecordset(tblBName, dbOpenSnapshot)
50        Debug.Print "RecNo " & " keyValue" & Space(7) & "fieldA" & Space(15) & " ValueA" & Space(25) & "ValueB"
60        Do While Not rsA.EOF
              'check each field in RSA
70            RecNo = RecNo + 1  'count each record in customerA
80            For i = 0 To rsA.Fields.Count - 1

                  'some values are Null
90                strA = IIf(Trim(rsA.Fields(i).Value) = "", rsA.Fields(i).Value, "-Spaces-")
100               strA = IIf(IsNull(rsA.Fields(i).Value), "??? NULL ???", rsA.Fields(i).Value)
110               strB = IIf(IsNull(rsB.Fields(i).Value), "??? NULL ???", rsB.Fields(i).Value)
120               strA = Replace(strA, vbCrLf, " ")
130               strB = Replace(strB, vbCrLf, " ")
                  'report differences
140               If strA <> strB Then
150                   Debug.Print RecNo & Space(6) & rsA.Fields(iPosKeyFld).Value & Space(11) & rsA.Fields(i).name & Space(20 - Len(rsA.Fields(i).name)) & strA & Space(30 - Len(strA)) & strB
160               End If
170           Next i
180           rsA.MoveNext
190           rsB.MoveNext
200       Loop

210       On Error GoTo 0
220       Exit Sub

Compare2TableFieldByField_Error:

230       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure Compare2TableFieldByField of Module AWF_Related"
End Sub

Here is a test routine showing how to call the procedure

Sub testcompare()
Dim a As String
Dim b As String
Dim i As Integer
a = "CustomerA"
b = "CustomerB"
i = 0
 Call Compare2TableFieldByField(a, b, i)
End Sub

Here is the output from my test tables

RecNo  keyValue       fieldA                ValueA                         ValueB
3      ANTON           ContactTitle        Owner                         Partner
6      BLAUS           ContactName         Hanna Moos                    Hanna Moose
16      CONSH           ContactName         Elizabeth Brown               Beth Brown
16      CONSH           Address             Berkeley Gardens 12  Brewery  Cornington Square 12  Brewery
16      CONSH           Phone               (171) 555-2282                (171) 456-2282
16      CONSH           Fax                 (171) 555-9199                (171) 545-9199
22      FISSA           ContactName         Diego Roel                    Rosa Martinez
24      FOLKO           City                Bräcke                        Stockholm
25      FRANK           ContactName         Peter Franken                 Pete Franken
39      KOENE           ContactName         Philip Cramer                 Phillip Cramer
42      LAUGB           Address             1900 Oak St.                  1900 Oak Street
48      LONEP           Region              OR                            Oregon
51      MEREP           ContactName         Jean Fresnière                J. Fresnière

If you copy the test routine and modify it as below and set i = the position of your keyfield (using 0,1,2,3...) 0 based

Sub testcompare()
Dim a As String
Dim b As String
Dim i As Integer
a = "TableA"
b = "TableB"
i = 0
 Call Compare2TableFieldByField(a, b, i)
End Sub

[webisti]

Hi Orange

Wow. You saved my day :-), if you will be in Prague, I own you a beer :-).
One more last thing, if I want to add comparison sampling, not just (i), but one or two more would that be more difficult?

Thanks a lot

Webisti

[orange]

Glad that it does what you need. Happy to help.

if I want to add comparison sampling, not just (i), but one or two more would that be more difficult?

I don't understand. Please provide an example of what you mean -- show a sample input and output.

(i) represents the field position in a record. It is iterated from 0 to rs.fields.count - 1

Recno counts records in TableA. It goes from 1 to TableA.Records.Count

[webisti]

OK for example I want to compare those two tables, but now where the "Left join B on A.MATNR = B.MATRN and A.WERKS = B.WERKS )
Taking the sample of the fields I wrote before.

[orange]

The previous procedure dealt with comparing each field in tableA with the same field in tableB. It did not involve joins.

What is the result of

SELECT  A.*,  B.*
FROM  A LEFT JOIN  B On
A.MATNR = B.MATRN and  A.WERKS = B.WERKS;

Are these fields correct??? A.MATNR = B.MATRN

[webisti]

Sorry mistype yes they are the same MATNR
basically this should serve to see one Material just once since the value can be for different plants..

so compare two tables where MATNR and WERKS are exactly the same values in both tables, for any difference in the remaining fields.

[orange]

Did you try the query?

[webisti]

Did you try the query?

will try tomorrow..now on the move. thanks for the tip Orange

[orange]

Once you do the select query you're down to 1 recordset. How do you compare? it? To what?

[webisti]

Well In both tables there are two fields that will always be the same MATNR and WERKS, the rest might change.
There are at least 600 reccords where the MATNR and WERKS are unique, I want to compare what was in table B before the changes done that will be seen in Table A.
To make it simple, Table A is the status of the reccords before the change and the table B, is the same but with changes done by users, I want to compare them and see if they did changed smth more that what they were supposed to,
That is the whole concept behind.

[orange]

Create 2 queries based on your tables and SAVE them.

QryA

SELECT * from TableA
WHERE MATNR & WERKS In
(
SELECT Distinct MATNR & WERKS FROM TableA
)

QryB

SELECT * from TableB
WHERE MATNR & WERKS In
(
SELECT Distinct MATNR & WERKS FROM TableB
) 
;

Then use the compareprogram

Sub testcompare()
Dim a As String
Dim b As String
Dim i As Integer
a = "QryA"
b = "QryB"
i = 0
 Call Compare2TableFieldByField(a, b, i)
End Sub

Good luck