By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,676 Members | 1,193 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,676 IT Pros & Developers. It's quick & easy.

comparing two tables

P: 8
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
Mar 1 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,365
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?
Mar 1 '07 #2

ADezii
Expert 5K+
P: 8,634
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:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2. Dim intCountOfFields As Integer, MyRS2 As DAO.Recordset
  3.  
  4. Set MyDB = CurrentDb()
  5. Set MyRS = MyDB.OpenRecordset("Table1", dbOpenDynaset)
  6. Set MyRS2 = MyDB.OpenRecordset("Table2", dbOpenDynaset)
  7.  
  8. Do While Not MyRS.EOF
  9.   If MyRS![Field1] <> MyRS2![Field1] Then
  10.       Debug.Print MyRS![Field1] & " <> " & MyRS2![Field1]
  11.   End If
  12.  
  13.   If MyRS![Field2] <> MyRS2![Field2] Then
  14.       Debug.Print MyRS![Field2] & " <> " & MyRS2![Field2]
  15.   End If
  16.  
  17.   If MyRS![Field3] <> MyRS2![Field3] Then
  18.       Debug.Print MyRS![Field3] & " <> " & MyRS2![Field3]
  19.   End If
  20.  
  21.   If MyRS![Field4] <> MyRS2![Field4] Then
  22.       Debug.Print MyRS![Field4] & " <> " & MyRS2![Field4]
  23.   End If
  24.   MyRS.MoveNext
  25.   MyRS2.MoveNext
  26. Loop
  27.  
  28. MyRS.Close
  29. MyRS2.Close
Mar 1 '07 #3

P: 8
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
Mar 2 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.