We help IT Professionals succeed at work.

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

3,595 Views
Last Modified: 2016-01-03
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 ?
Comment
Watch Question

aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
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.

Author

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

Thanks
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
Top Expert 2015

Commented:
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.
Microsoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
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

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
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 ?
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Commented:
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
aikimarkSocial distance; Wear a mask; Don't touch your face; Wash your hands for 20 seconds
CERTIFIED EXPERT
Top Expert 2014

Commented:
that overflow is probably the result of an Integer data type used for some iteration variable.  Change the definition to Long

Author

Commented:
ProfessorJimJam,
 
 Can you please share so i can test ?
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Commented:
sharepoint0520

attached is THE one with Formula which is more of a traditional way.
ee.xlsx
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Commented:
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

Author

Commented:
Thanks
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Commented:
You are most welcome

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.