Link to home
Start Free TrialLog in
Avatar of sharepoint0520
sharepoint0520

asked on

How to compare all columns in two tables in MS access or excel ?

Experts,

 I have two tables with 50000 records with 100 columns.  Both have same structure . Now i have to compare Original tables with Current tables and find out how many rows has different value and which column has ? So i can make the update query and update only those records.

Do we have any macro or any tool to compare two tables in excel or access ?
Avatar of aikimark
aikimark
Flag of United States of America image

In Access, you join the two tables on their Primary Key columns.  Then you construct the where clause looking for differences.  With 100 columns, this query string would probably be constructed programmatically.
Example:
Select O.*, C.*
From Original As O Inner Join Current As C On O.ID = C.ID
Where 
O.C1<>C.C1 OR
O.C2<>C.C2 OR
O.C3<>C.C3 OR
O.C4<>C.C4 OR
O.C5<>C.C5

Open in new window

Where the C# represents column names.
Avatar of sharepoint0520
sharepoint0520

ASKER

Hi ,
 Is it possible for you to write query or loop for all colums?

Thanks
if you want to keep track of what is different, it is better to compare each column one at a time.  This could be done in a loop and you could record where the differences are in another table.  Each of the tables should have an AutoNumber field (add one if the table doesn't already have it) for recording in the changes table which would have a structure something like this:

Chgs
- ChgID, Autonumber, PK (Primary Key)
- FldID, Long Integer, FK (Foreign Key to Flds) (assume this is mapped to a field in table 2)
- ID1, Long Integer, FK to Autonumber field in table 1
- ID2, Long Integer, FK to Autonumber field in table 2
- Val1, text, value 1
- Val2, text, value 2

Flds
- FldID, Autonumber, PK (Primary Key)
- TID, Long Integer, FK (Foreign Key to Tbls)
- Fldname, text, 64, field name

if the fieldnames are different in each table, you will also need a table to map them

Tbls
- TID, Autonumber, PK (Primary Key)
- Tblname, text, 64, tablename

If your data is in Access, you can run the free Analyzer for Microsoft Access to get a list of fields in each table.

http://analyzer.codeplex.com

Once you have the definitions set up, if you want to go this route, post what you have and we can help you with the code.  We will need to know more about how you are choosing records to compare as well.
ASKER CERTIFIED SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Something like this
dim strWhere as String
dim fld as field
strWhere = "(1 = 0) "
for each fld in dbEngine(0)(0).TableDefs("Original").fields
    strWhere = strWhere & " Or " & "O.[" & fld.name & "] <> C.[" & fld.name & "] "
next

Open in new window

Given that you are starting with 100 columns and 255 is the Access limit, you will need at least three queries.  You need to return the table1 value, the table 2 value and probably a different flag to make querying easier.  If you don't want the different flag then you could do it with a single query.

The spreadsheet download is probably the simplest solution but with 50000 records in each table, the vlookups could be very slow.

Of course, in Access, the whole comparison is predicated on the assumption that each row has a unique identifier and those identifiers will allow the tables to be joined.  In Excel, since it is not a relational database, the match can be row by row so if the row counts are identical and the sets are sorted in the correct order, the match will be row to row rather than key to key.
ProfessorJimJam,

 It was good example and working fine with small amount of data. But when i ran for 50000 records i got error. " Run time error '6:  Overflow".

And i am care about only difference so i can take those and update. Is it possible to get only those records and put in another tab ?
What about formula solution? It might take couple of minties to calculate but is doable

I have another version of vba but I am afraid your machine memory will not handle it for 5000 rows
that overflow is probably the result of an Integer data type used for some iteration variable.  Change the definition to Long
ProfessorJimJam,
 
 Can you please share so i can test ?
sharepoint0520

attached is THE one with Formula which is more of a traditional way.
ee.xlsx
if you do not want this, you may want to try with attached Workbook for comparing sheets.

see the parameter sheet . i am not the author of this macro. author of this macro is Alan from MrExcel forum.
MAINMACRO.xlsb
Old-WorkBook.xlsx
New-WorkBook.xlsx
Thanks
You are most welcome