MINUS/EXCEPT in Microsoft Access

Microsoft Access is not supporting Minus or Except.

If , we want to find the difference in data between two tables we can use the below syntax in Access.

--MINUS IN ACCESS

SELECT

*

FROM

Table1

WHERE

(((Exists (SELECT * FROM Table2 WHERE Table1.field1 = Table2.field1 AND Table1.field2 =Table1.field2………………))=False));

Example:

                                                                            

Suppose we have two table name SRC and TGT and we want to find difference of the records which are not in target.

In Oracle we can write the simple query and can find the difference.

--Minus in  SQL

SELECT EMPNO,ENAME FROM SRC

MINUS

SELECDT EMPNO,ENAME FROM TGT;

But, Since ACCESS is not supporting Minus/Except. So we can find the difference between SRC and TGT table Using Below SQL.

SELECT EMPNO,ENAME FROM

SRC

WHERE

(((Exists (SELECT EMPNO,ENAME FROM TGT WHERE SRC.EMPNO = TGT.EMPNO AND SRC.ENAME =TGT.ENAME))=False));