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));

Populate SCD3 from SCD2.

SELECT * FROM
(
SELECT A.SK,A.EID,A.ST_DT,A.ED_DT  FROM EMP2 A
INNER JOIN
(SELECT MAX(ED_DT) AS N_ED_DT , EID FROM EMP2 GROUP BY EID) B
ON A.ST_DT=B.N_ED_DT

UNION ALL

SELECT A.SK,A.EID,A.ST_DT,A.ED_DT   FROM EMP2 A
INNER JOIN
(SELECT MAX(ED_DT) AS N_ED_DT , EID FROM EMP2 GROUP BY EID) B
ON A.ED_DT=B.N_ED_DT
) C
ORDER BY C.EID

Virtual Column in Where clause

Question: Can we call virtual column in the where clause.

Answer: Yes, we can.

Description:

Suppose we have the Standard EMP table (as per oracle Scott user) and want to assign the job title on the basis of SAL column if SAL between 1000 and 3001 then JOB_TITLE is 'JUNIOR' and if SAL between 3001 and 5000 then JOB_TITLE is 'SENIOR' else JOB_TITLE is 'TRAINEE'.

                                                                  Now, we want to fetch the records that have the JOB_TITLE as ‘TRAINEE’ only.  Here JOB_TITLE is the virtual column (this is not physically in EMP table), so we can’t filter directly in WHERE clause.

Ok let’s try,

SELECT EMPNO, ENAME, SAL, DEPTNO,

CASE

 WHEN SAL >=1000  AND SAL<=3000 THEN 'JUNIOR'

 WHEN SAL >=3001  AND SAL<=5000 THEN 'SENIOR'

 ELSE 'TRAINEE'

 END AS JOB_TITLE

FROM EMP

WHERE JOB_TITLE = 'TRAINEE'; 

The above query will return the below ERROR.

Error: Invalid column name 'JOB_TTILE'.

To resolve this Error we can write the same query in different manner.

Method 1

SELECT EMPNO, ENAME, SAL, DEPTNO,

CASE

 WHEN SAL >=1000  AND SAL<=3000 THEN 'JUNIOR'

 WHEN SAL >=3001  AND SAL<=5000 THEN 'SENIOR'

 ELSE 'TRAINEE'

 END AS JOB_TITLE

FROM EMP

WHERE

(CASE

 WHEN SAL >=1000  AND SAL<=3000 THEN 'JUNIOR'

 WHEN SAL >=3001  AND SAL<=5000 THEN 'SENIOR'

 ELSE 'TRAINEE'

 END AS JOB_TITLE) = 'TRAINEE'

Method 2

SELECT EMPNO, ENAME, SAL, DEPTNO, JOB_TITLE

FROM

(

SELECT EMPNO, ENAME, SAL, DEPTNO,

CASE

 WHEN SAL >=5000  AND SAL<=10000 THEN 'JUNIOR'

 WHEN SAL >=10001 AND SAL<=20000 THEN 'SENIOR'

 ELSE 'TRAINEE'

 END AS JOB_TITLE

FROM EMP

)

WHERE JOB_TITLE='TRAINEE'

In the Method 1 we try to take full virtual column in WHERE clause and in Method 2 we try to solve this problem with the IN LINE view.

Fetching records one with Null values another with proper value.

                  Table: EMP

     

Empno	Ename
100	Jack
100
101	Neha
102	June
103	Miller
104
105	Pam
105

Suppose we have the above records in the table, which have duplicate Empno , one with Ename and another with Null. For example Empno 100 has two value one is Jack another is NULL, same case with Empno 105 ( Empno 104 which has only one value, that is Null).

Scenario: :- Fetch the records from the above table and result should be like below Table.

Empno	Ename
100	Jack
101	Neha
102	June
103	Miller
104
105	Pam

Query:  

SELECT

CCC.EMPNO

,CASE WHEN CCC.ENAME=999 THEN NULL ELSE CCC.ENAME END AS ENAME

FROM

(

SELECT

BBB.EMPNO

,BBB.ENAME

,DENSE_RANK() OVER

    (PARTITION BY BBB.EMPNO ORDER BY BBB.ENAME DESC) AS Rank

FROM

(

SELECT

AAA.EMPNO

,CASE WHEN AAA.ENAME IS NULL THEN 999 ELSE AAA.ENAME END AS ENAME

FROM

(

SELECT

distinct EMP.EMPNO,ENAME FROM EMP

) AS AAA

) AS BBB

) AS CCC

WHERE CCC.RANK = 1 ;