Compare Two Tables [DETAILED PROBLEM DESCRIPTION]

[vetabz]

Hi,



The problem is simple but somehow the solution eludes me!

There's basically two structure-identical tables but one had more rows and some data(cells) in some rows are different from each other:

shTableOne(illustrated in Excel)


shTableTwo(Illustrated in Excel)



So I wanted to pinpoint where the changes did occur(i possible at the data level) but if not even just at the row level.

I tried this solution: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
But I could not get it to work!
Attached the MS Access File I have worked on.
CompareTwoTables.zip

[andy49]

CompareTwoTables.zip

I created the union table separately but I think this is what you need

[ranman256]

if you actually have 20 columns to check,
you will need 20 separate OUTER join queries , each will compare each single column
All items in Tbl1 but missing in Tbl2

AND (if needed) then 20 separate OUTER join queries , All items in Tbl2 but missing in Tbl1

thats 40 queries in a macro.
(or put all in to a single UNION query)

[vetabz]

This is awesome @andy49, awesome indeed - lost more than half of my day trying to solve this. I actually resorted to VBA (DAO) on the later part but SQL approach is much more efficient just like what you did here. Thanks a lot!!!

I will also try to see if I could work out your suggestion @ranman256, but I think I can achieve that using @andy49's solution plus VBA(DAO).

[andy49]

Glad it helped.


Sent from my iPhone using Tapatalk