1

Is it possible to create a query and ask the following? I have two tables (each one has one column with data) and I want to obtain as a result all data from the first table MINUS the data from the second table.

1

In standard SQL you could do that very easily with EXCEPT:

SELECT
  Column1
FROM
  Table1

EXCEPT

SELECT
  Column2
FROM
  Table2;

However, EXCEPT is unsupported in MS Access. Instead, you could use an anti-join. There are various ways of implementing an anti-join. One is using an EXISTS predicate:

SELECT
  Column1
FROM
  Table1 AS t1
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      Table2 AS t2
    WHERE
      t1.Column1 = t2.Column2
  );
0

Another workaround that works with Access is to use a LEFT JOIN on the criteria that the ID variable in the exclusion table IS NULL

SELECT IDvar
    FROM Table1 
LEFT JOIN Table2 
ON Table1.IDvar = Table2.IDvar
    WHERE Table2.IDvar IS NULL;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.