comparing two tables

[hasnain]

Dear All,
I want to compare two tables.
Both of the tables have same fields but their location in terms of columns can be different.

My target is to get those fields only whose values are found different on comparing.

Regards
Hasnain

[Rabbit]

We need more information. i.e. What fields are in the tables, how are they related, what kind of data do the tables hold, and when you say compare them, how is the comparison being made?

[ADezii]

Dear All,
I want to compare two tables.
Both of the tables have same fields but their location in terms of columns can be different.

My target is to get those fields only whose values are found different on comparing.

Regards
Hasnain

With limited information, I'll have to make some assumptions:
__1 Table Names: Table1 and Table2
__2 Number of Fields: 4 in each Table
__3 Field Names: Field1, Field2, Field3, Field4
__4 Number of Records in each Table: irrelevant (as long as equal)
__5 This code is adaptable to include Record Numbers, more/less Fields, etc.

To find the differences between Field values in each Table:

Dim MyDB As DAO.Database, MyRS As DAO.Recordset
Dim intCountOfFields As Integer, MyRS2 As DAO.Recordset
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Table1", dbOpenDynaset)
Set MyRS2 = MyDB.OpenRecordset("Table2", dbOpenDynaset)
 
Do While Not MyRS.EOF
  If MyRS![Field1] <> MyRS2![Field1] Then
      Debug.Print MyRS![Field1] & " <> " & MyRS2![Field1]
  End If
 
  If MyRS![Field2] <> MyRS2![Field2] Then
      Debug.Print MyRS![Field2] & " <> " & MyRS2![Field2]
  End If
 
  If MyRS![Field3] <> MyRS2![Field3] Then
      Debug.Print MyRS![Field3] & " <> " & MyRS2![Field3]
  End If
 
  If MyRS![Field4] <> MyRS2![Field4] Then
      Debug.Print MyRS![Field4] & " <> " & MyRS2![Field4]
  End If
  MyRS.MoveNext
  MyRS2.MoveNext
Loop
 
MyRS.Close
MyRS2.Close

[hasnain]

explaining my query.......... ......
first consider both of the tables have same number of fields and name of fields are same as well.
Let say I have 10 fields and I want to compare 10 fields of table 1 from table 2.
As far as values in the fields are concerned, they can be any number like 1, 5000, 32,2,0 etc.
I mean comparing like if one of the fields has value 1 in table one and in table 2, the same field is having value 2 instead of 1, so I want to find out only these changes.
Result can be shown in any way