CODEWITHSUNDEEP
sqlms-access
vuyy1182
vuyy1182

Reputation: 1686

How to compare two tables access sql

I have two tables with same no.of columns and same column names , datatype of columns are same as each other but one of the table will have some extra records. How do i find those.

Table 1

        Name     Description       EID  

       name1      ssdad          1001
       name2      gfd            1002
       name3      gfdsffsdf      1003

Table 2

        Name     Description       EID  

       name1      ssdad          1001
       name2      gfd            1002
       name3      gfdsffsdf      1003
       name2      gfd12344       1002
       name3      gfdsffsdf      1003
       name5      gfd            1002
       name6      gfdsffsdf      1003

How do i specify that in query

Upvotes: 1

Views: 4837

Answers (2)

crthompson
crthompson

Reputation: 15875

To find those extra in table2:

select 
   *
from
  table1 t1
  left join table2 t2 on t1.name = t2.name 
                     and t1.description = t2.description 
                     and t1.eid = t2.eid
where
  t1.name is null

Upvotes: 2

Richard Pascual
Richard Pascual

Reputation: 2021

Solving Query Requirements Through the Access Query Wizard

SQL is the best route in this situation. I tried looking at built-in ACCESS db functionality because it is likely that most people who find this solution think in the style of the Access Database ecosystem. Anyways, it might be nice to show an Access UI-friendly design approach...

These are my two tables and their sample data:

Table 1: (Has more records)

Find Unmatched Data Query in Access: Sample Table 1

Table 2:

Find Unmatched Data Query in Access: Sample Table 2

There is a wizard which designs a query object which resembles the OP's request:

Access: Find Unmatched Query Wizard Option

Opting to further modify the design however, reveals a limitation: A given table relationship cannot define more than TWO JOINS MAXIMUM. The three column example of the OP would not be satisfied with additional work.

Find Unmactched Query Joins

Alternate Approach Through Subqueries

An alternate translation of a three-column equi-join is a concatenated string representing all three column values inside of a single column. This would get over the join limitation. I created Query1 and Query2 which selected all columns from Table1 and Table2 (respectively), with a third column defined by the following Access DB expression builder:

 -- For Query1:
 [Table 1]![Name_Col] + [Table 1]![Description_Col] + Str( [Table 1]![EID] )     


 -- For Query2:
 [Table 2]![Name_Col] + [Table 2]![Description_Col] + Str( [Table 2]![EID] )

Applying the same wizard, except for joining Query1 and Query2 on the new COMPOSITE KEY value derived from the three original data columns.

Joining Sub Query Results on Composite Join Key Value

The Final Results:

Find Unmatched Results Between Tables

So Access DB users have their own native approaches to set operations and data manipulation. Now that there is both a SQL and an ACCESS db approach posted in response, there can be some comparison analysis of how each approach decomposes the problem at hand. Enjoy!

Upvotes: 3

Related Questions