compare all the records of all the fields of two tables

  • Thread starter Thread starter Ruskin Hardie
  • Start date Start date
R

Ruskin Hardie

Standard Access does this, in the Query design.... Choose 'New' and select
'Find Unmatched Query Wizard'
 
Does some exist it sorts things out of to compare all the records of all the
fields of two tables and to see that this different one among the tables?

Each table has 90 fields


example

table 1 table 2
field1=a field1 = b
field2=g field2 =m
 
Yeah... that could be a problem...

Unfortunately, there is no easy way in a query, but you could code
something, if all your fields are the same in each table... But the
following will compare each record in the two tables (where record 1 of
table 1, should match record 2 of table 2 and so on...)

Sub CheckValues()
Dim x As Long
Dim Rec1 As DAO.Recordset
Dim Rec2 As DAO.Recordset
Dim myCount As Long

Set Rec1 = CurrentDb().OpenRecordset("SELECT * FROM Table1;")
Set Rec2 = CurrentDb().OpenRecordset("SELECT * FROM Table2;")

Rec1.MoveFirst
Rec2.MoveFirst
myCount = 0

Debug.Print "Rec Num, Field Num, Table 1, Table 2"
Do Until Rec1.EOF
myCount = myCount + 1
For x = 0 To 89
If Rec1.Fields(x).Value <> Rec2.Fields(x).Value Then
Debug.Print myCount & ", " & x & ", " &
Rec1.Fields(x).Value & ", " & Rec2.Fields(x).Value
End If
Next x
Rec1.MoveNext
Rec2.MoveNext
Loop
End Sub
 
Yikes... Meant to say, that the example, expects record 1 of Table1 to match
record 1 of Table2, then record 2 of Table1 should match record 2 of Table2
and so on... By placing a Do/Loop inside the current Do/Loop, where we loop
until Rec2.EOF, then we can compare each record of table 1, with every
record in Table2. So you can play with this code and you'll see what I
mean...

Also, meant to add, that I forgot to close the recordset objects (oopss)...
 
Pardon, but as it would be now the code?


Ruskin Hardie said:
Yikes... Meant to say, that the example, expects record 1 of Table1 to match
record 1 of Table2, then record 2 of Table1 should match record 2 of Table2
and so on... By placing a Do/Loop inside the current Do/Loop, where we loop
until Rec2.EOF, then we can compare each record of table 1, with every
record in Table2. So you can play with this code and you'll see what I
mean...

Also, meant to add, that I forgot to close the recordset objects (oopss)...
Click to expand...
 
Sorry, not sure I understand your question.... Are you asking for the new
code? NOTE: this routine will compare record 1 of table1 and make sure that
record 1 of table2 is the same. Then it will compare record 2 of table 1
against record 2 of table 2 to see if they are the same, then record 3 of
table 1 against record 3 of table 2, etc.... It will also tell you, if the
number of records in table1 are different to the number of records in
table2...

Sub CheckTable()
Dim x As Long
Dim Rec1 As DAO.Recordset
Dim Rec2 As DAO.Recordset
Dim myCount As Long

Set Rec1 = CurrentDb().OpenRecordset("SELECT * FROM Table1;")
Set Rec2 = CurrentDb().OpenRecordset("SELECT * FROM Table2;")

Rec1.MoveFirst
Rec2.MoveFirst
myCount = 0

Debug.Print "Rec Num", "Field 1", "Value 1", "Field 2", "Value 2"
Do Until Rec1.EOF Or Rec2.EOF
myCount = myCount + 1
For x = 0 To 89
If Rec1.Fields(x).Value <> Rec2.Fields(x).Value Then
Debug.Print myCount, _
Rec1.Fields(x).Name, _
Rec1.Fields(x).Value, _
Rec2.Fields(x).Name, _
Rec2.Fields(x).Value
End If
Next x

Rec1.MoveNext
Rec2.MoveNext

If (Rec1.EOF And Not Rec2.EOF) Or (Not Rec1.EOF And Rec2.EOF) Then
Debug.Print "NUMBER OF RECORDS IN TABLES DON'T MATCH"
End If
Loop

Rec1.Close
Rec2.Close
Set Rec1 = Nothing
Set Rec2 = Nothing
End Sub
 
It is really almost that that I want, you are very good same.

only lacks a thing:

I want him to feel the different from the field1 of the table1 field1 of the
table2 and later all the different field2 of the tabela1 and field2 of the
table2

Ok.
Thank you very much for helping me.
 
Still not sure I understand exactly what you are after. Maybe if you said
what you are trying to achieve..

EG: if Table1 has 10 records and Table2 has 10 records, what happens? Do we
check record 1 of table1 against record 1 of table2, or check record1 of
table1 against ALL 10 records of table2, then do the same for record 2 of
table1....

If Table1 is like a master table, and only has 1 record, then you want to
check all records in Table2, to see what fields are different, then this is
quite easy, with only small changes to the code below.
 
example

table1 table2 table1
table2
field1=1 field1=2 field2=8
field2 =5
field1=5 field1=6 field2=9
field2=6

field2=10 field2=11
 
Ok, am now assuming, there is only one record in both Table1 and Table2. In
this case, the CallRoutine sub routine below, allows you to pass the field
number and get the values of the field from table1 and table2....

Sub CallRoutine()
Dim Tab_1_Field As String
Dim Tab_2_Field As String

ReturnTableField 1, Tab_1_Field, Tab_2_Field

If Tab_1_Field <> Tab_2_Field Then
MsgBox "Fields are different: " & _
"Table1: " & Tab_1_Field & " " & _
"Table2: " & Tab_2_Field
Else
MsgBox "Fields are the same."
End If
End Sub

Sub ReturnTableField(FieldNumber As Long, _
ByRef Table_One_Value As String, _
ByRef Table_Two_Value As String)

Dim Rec1 As DAO.Recordset
Dim Rec2 As DAO.Recordset

Set Rec1 = CurrentDb().OpenRecordset("SELECT * FROM Test1;")
Set Rec2 = CurrentDb().OpenRecordset("SELECT * FROM test2;")

Rec1.MoveFirst
Rec2.MoveFirst

Table_One_Value = Rec1.Fields(FieldNumber - 1).Value
Table_Two_Value = Rec2.Fields(FieldNumber - 1).Value

Rec1.Close
Rec2.Close
Set Rec1 = Nothing
Set Rec2 = Nothing
End Sub
 
Ok, so the below will work fine in this case.... But instead of
'Debug.Print', you could insert the record and field into a table and then
print the records from that table... To do this, at the beginning of the
code add;

DoCmd.RunSQL "DELETE * FROM [Table Name];"

and where the Debug.Print is, in the loop, add;

DoCmd.RunSQL "INSERT INTO [Table Name] ([Record Num]," & _
"[Field Num],[Tab 1 Value],[Tab 2 Value]) VALUES (" & _
myCount & "," & x + 1 & ",'" & Rec1.Fields(x).Value & "','" & _
Rec2.Fields(x).Value & "');"
 
EG: if Table1 has 10 records and Table2 has 10 records, what happens? Do we
check record 1 of table1 against record 1 of table2

That that I want, therefore I want to create a report of that and also to
send the result for a temporary table or excel.
 
didn't give right, he simply informs that "Fields are the same" and she
leaves doesn't verify the other fields.
 
It is really almost that that I want, it lacks we just place the records in the correct fields in the table.

eg:

table1 table2 table1 table2
field1=4 field1=5 field2=8 field2=9
field1=8 field1=7 field2=6 field2=23
field2=9 field2=14

and and so on




Ruskin Hardie said:
Ok, so the below will work fine in this case.... But instead of
'Debug.Print', you could insert the record and field into a table and then
print the records from that table... To do this, at the beginning of the
code add;

DoCmd.RunSQL "DELETE * FROM [Table Name];"

and where the Debug.Print is, in the loop, add;

DoCmd.RunSQL "INSERT INTO [Table Name] ([Record Num]," & _
"[Field Num],[Tab 1 Value],[Tab 2 Value]) VALUES (" & _
myCount & "," & x + 1 & ",'" & Rec1.Fields(x).Value & "','" & _
Rec2.Fields(x).Value & "');"

Frank Dulk said:
EG: if Table1 has 10 records and Table2 has 10 records, what happens? Do we
check record 1 of table1 against record 1 of table2

That that I want, therefore I want to create a report of that and also to
send the result for a temporary table or excel.


table 2
Click to expand...
Click to expand...
 
Back
Top