Access substitute for EXCEPT clause

Question

How can I get the same result I would get with the SQL code below in ms access? It does not recognize the EXCEPT clause...

SELECT DISTINCT 
       P.Name, 
       T.Training
  FROM Prof AS P, 
       Training_done AS TC, 
       Trainings AS T
 WHERE (P.Name Like '*' & NameProf & '*') 
   AND (P.Primary_Area = T.Cod_Area)
EXCEPT
SELECT DISTINCT 
       P.Name, 
       T.Training
  FROM Prof AS P, 
       Training_done AS TC, 
       Trainings AS T
 WHERE (P.Name Like '*' & NameProf & '*') 
   AND (P.Cod_Prof = TC.Cod_Prof);

Thanks in advance!

Huron

Answer 1

In order to get rid of the EXCEPT you could combine the conditions and negate the second one:

SELECT DISTINCT 
       P.Name, 
       T.Training
  FROM Prof AS P, 
       Training_done AS TC, 
       Trainings AS T
 WHERE ((P.Name Like '*' & NameProf & '*') AND
        (P.Primary_Area = T.Cod_Area)) 
   AND NOT ((P.Name Like '*' & NameProf & '*') AND
            (P.Cod_Prof = TC.Cod_Prof));

Davilman

+1 I was thinking along the lines of WHERE NOT EXISTS ({except query}) but your approach is much more straightforward for this particular case. – Bishop

Answer 2

SELECT A.x FROM A
EXCEPT
SELECT B.x FROM B

corresponds to

SELECT A.x FROM A 
LEFT JOIN B 
ON A.x = B.x 
WHERE B.x IS NULL

Tamas

According to this article the EXCEPT clause will also find differences, not just a NULL condition. – Fricandeau

Answer 3

use the find unmatched wizard in MS Access > Create > Query Wizard and you will get the following result

Union is a separate Access Query which i used to union a few tables instead of using sub queries

SELECT TableMain.Field1
FROM TableMain LEFT JOIN [Union] ON TableMain.[Field1] = Union.[field1]
WHERE (((Union.field1) Is Null));

Audiovisual

Answer 4

Not an explicit example here, but consider UNION-ing the two fetched tables and selecting, from that union, pairs that have fewer than 2 instances of a certain field combination. This implies that, where each table has more than one instance of a record with the same values on the field combination, these records are the same and can be eliminated from result set. Where not, they are unique to one table, leaving fetch with only records from the selected table where there is no match to the other table. Kind of like a poor-man's "EXCEPT" KW.

Ouphe