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 ?
Microsoft AccessMicrosoft ExcelVisual Basic ClassicMicrosoft ApplicationsProgramming

Avatar of undefined
Last Comment
Professor J

8/22/2022 - Mon
aikimark

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.
ASKER
sharepoint0520

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

Thanks
crystal (strive4peace) - Microsoft MVP, Access

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Professor J

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
aikimark

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

PatHartman

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.
ASKER
sharepoint0520

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 ?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Professor J

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
aikimark

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

ProfessorJimJam,
 
 Can you please share so i can test ?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Professor J

sharepoint0520

attached is THE one with Formula which is more of a traditional way.
ee.xlsx
Professor J

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
ASKER
sharepoint0520

Thanks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Professor J

You are most welcome