Compare 2 tables in MS Access based on multiple fields and get a new table with values which are not contained in the first table

Question

I´m trying to write a SQL code in MS Access what would be able to compare multiple fields from 2 tables and create a new one with all values where at least one field from table 2 is not equal to a field from table one.

t1:
ID personalID lastname   firstname   city   postcode street   houseNumber
1  1456       lastname01 firstname01 city01 4789     street01 46 
2  5783       lastname02 firstname02 city02 1239     street02 21
3  1698       lastname03 firstname03 city03 6578     street03 98

t2:
ID personalID lastname   firstname   city   postcode street   housenumber 
2  5783       lastname02 firstname02 city99 1593     street99 29
3  1698       lastname03 firstname03 city03 6578     street03 98
4  2517       lastname04 firstname04 city04 7321     street04 7

expected result:

ID personalID lastname   firstname   city   postcode street   housenumber 
2  5783       lastname02 firstname02 city99 1593     street99 29
4  2517       lastname04 firstname04 city04 7321     street04 7

in 2: new address (new city postcode street and housenumber)

in 4: new field

Both tables are connected via the ID-key.

My code:

SELECT
    *
FROM
    t2
    RIGHT JOIN t2
    ON
               t2.personalID = t1.personalID
        AND    t2.city = t1.city
        AND    t2.postcode = t1.postcode
        AND    t2.street = t1.street
        AND    t2.houseNumber = t1.houseNumber
WHERE
          t2.personalID IS NULL
    OR    t2.city IS NULL
    OR    t2.postcode IS NULL
    OR    t2.street IS NULL
    OR    t2.houseNumber IS NULL; 

Error: Syntaxerror in JOIN-Operation

I suppose the RIGHT JOIN Operation is correct since I want to join t2 to t1 (but only show values in t2) but I´m not sure if the AND or the OR operator is correct (in the case the personalID exists in both tables but only some fields (e.g. street) has been changed).

Thx for your help in advance.

Answer 1

MS Access syntax is different from SQL Server's. Try this:

SELECT t2.*
FROM
      t2 LEFT JOIN t1 ON (t2.city = t1.city)
  AND (t2.postcode = t1.postcode)
  AND (t2.street = t1.street)
  AND (t2.houseNumber = t1.houseNumber)
  AND (t2.personalID = t1.personalID)
WHERE (((t1.ID) Is Null));

p.s. I'd rather use visual designer in MS Access to do such queries

p.p.s. your code would have no syntax error like this (you had a typo - t2 RIGHT JOIN t2):

SELECT
    *
FROM
    t2
    RIGHT JOIN t1
    ON
               t2.personalID = t1.personalID
        AND    t2.city = t1.city
        AND    t2.postcode = t1.postcode
        AND    t2.street = t1.street
        AND    t2.houseNumber = t1.houseNumber
WHERE
          t2.personalID IS NULL
    OR    t2.city IS NULL
    OR    t2.postcode IS NULL
    OR    t2.street IS NULL
    OR    t2.houseNumber IS NULL; 

but this query results are different from your described result, and variant with t2 LEFT JOIN t1 works as you mentioned