Comparing Two Tables in MS Access using SQL

Question

I'm trying to compare two tables in MS Access using SQL. Each table has two columns. TableA has less rows than TableB and everything in TableA should also be in TableB. I would like to check that the rows in TableA are identical to the corresponding rows in TableB by displaying the rows that do not match up.

Here's where I run into a problem. I know how to compare the rows, but not how to compare the columns simultaneously. So right now if the rows in column1 of TableA match up to the rows of column1 in TableB, my query comes up empty even if some rows in column2 of TableA do not match up to some rows of column2 in TableB, and vice versa.

This is my current (generic) SQL code:

SELECT TableA.*
FROM TableA LEFT JOIN TableB ON TableA.column1 = TableB.column1
WHERE TableA.column1 Is Null;

Does anyone know how to modify this code to check both columns simaltaneously? I want the columns to be treated as one joined entity rather than as separate ones.

Thank you!

Edit: I figured out how to do it. A couple of the answers put me on the right path, but none of them were complete. This is what I ended up doing. Perhaps it will help someone else.

SELECT TableA.*
FROM TableA LEFT JOIN TableB ON TableA.column1 = TableB.column1
    AND TableA.column2 = TableB.column2
WHERE TableA.column1 Is Null OR TableA.column2 Is Null;

Great answer, Sam - will be useful to me as well. Access' SQL is quite limited, but the usual way I would have done this a correlated subquery: SELECT a.* FROM TableA a WHERE NOT EXISTS (SELECT 1 FROM TableB WHERE column1 = a.column1 AND column2 = a.column2) — VBlades
– VBlades, Commented Jul 3, 2014 at 20:52

Iłya Bursov · Accepted Answer · 2014-06-30 20:22:20Z

1

I suppose you need something like this:

SELECT 'Row only in tableA', TableA.column1, TableA.column2
FROM TableA
LEFT JOIN
TableB ON TableA.column1 = TableB.column1 and TableA.column2 = TableB.column2
WHERE TableA.column1 <> TableB.column1 or TableA.column2 <> TableB.column2
union all
SELECT 'Row only in tableB', TableB.column1, TableB.column2
FROM TableB
LEFT JOIN
TableA ON TableA.column1 = TableB.column1 and TableA.column2 = TableB.column2
WHERE TableA.column1 <> TableB.column1 or TableA.column2 <> TableB.column2;

answered Jun 30, 2014 at 20:22

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Iłya Bursov Over a year ago

@SamIAm what exactly didn't work? results are wrong, syntax error?

SamIAm Over a year ago

I'm not sure. I just know that it didn't pick up the known errors.

SamIAm Over a year ago

I think the UNION made it unnecessarily complicated.

Iłya Bursov Over a year ago

@SamIAm union here just to get both kind of errors, I suppose your problem because of incorrect translation of this template to actual query, check table and column names

SamIAm Over a year ago

I just meant that the union wasn't necessary-I solved the problem by putting in an AND and an OR. Since I solved the problem I'm not going to bother debugging this code, but thanks anyway!

D Stanley · Accepted Answer · 2014-06-30 20:24:13Z

0

It's a little unclear, but it sounds like you want something like:

SELECT TableA.*
FROM TableA LEFT JOIN TableB ON TableA.column1 = TableB.column1
WHERE 
    Tableb.column1 Is Null // not found in Tableb
    OR Tablea.Column2 <> Tableb.Column2  // column2 does not match

answered Jun 30, 2014 at 20:24

D Stanley

153k12 gold badges190 silver badges257 bronze badges

1 Comment

SamIAm Over a year ago

The problem with this is that it's still checking the columns separately-I need the combination of the two columns to be identical.

VBlades · Accepted Answer · 2014-06-30 21:47:13Z

0

I think this should work for you. I have coded this VERY defensively because I don't know what datatypes you are working with. What we try to do is create a key from ALL the columns in the tables, and join on that. To find out what is exactly the same in both tables:

SELECT TableA.*
FROM TableA INNER JOIN TableB ON CStr(Nz(TableA.column1, "")) & "_" & CStr(Nz(TableA.column2, "")) = CStr(Nz(TableB.column1, "")) & "_" & CStr(Nz(TableB.column2, ""));

to find out what is missing in TableA:

SELECT TableB.*
FROM TableA RIGHT JOIN TableB ON CStr(Nz(TableA.column1, "")) & "_" & CStr(Nz(TableA.column2, "")) = CStr(Nz(TableB.column1, "")) & "_" & CStr(Nz(TableB.column2, ""))
WHERE TableA.column1 IS NULL;

The idea is that we create one key from all the columns so we can work as if it was just an outer join with one column.

EDIT: Oops, flipped my Nz and CStr calls.

edited Jun 30, 2014 at 21:47

answered Jun 30, 2014 at 21:40

VBlades

2,2511 gold badge14 silver badges8 bronze badges

4 Comments

SamIAm Over a year ago

Why did you use a right join instead of a left join?

VBlades Over a year ago

@SamIAm: No worries. Every LEFT JOIN can be represented as a RIGHT JOIN and vice versa. I did it so I didn't have to retype the text.

SamIAm Over a year ago

That I know. But I was looking for the errors in Table A-doesn't this show the errors in Table B?

VBlades Over a year ago

@SamIAm: Btw, why I did it that way is because I thought you wanted to see the row in B which were not in A (missing from A). Anyway, you figured it out.

Robert Co · Accepted Answer · 2014-06-30 22:20:53Z

0

SELECT TableA.*
  FROM TableA 
  LEFT JOIN TableB 
    ON TableA.column1 = TableB.column1
   AND TableA.column2 = TableB.column2
WHERE TableA.column1 Is Null;

answered Jun 30, 2014 at 22:20

Robert Co

1,7358 silver badges14 bronze badges

Collectives™ on Stack Overflow

Comparing Two Tables in MS Access using SQL

4 Answers 4

5 Comments

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related