Access Substitute for Except Clause

Access substitute for EXCEPT clause

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

UNION does not work in access

Access SQL doesn't have EXCEPT.

You'll have to use WHERE NOT EXISTS or an outer join.

Access substitute for clause except may help to find a workaround.

SQL subqueries alternative to INTERSECT

Do not know how the performance is .. try it ..

SELECT P.Name 
  FROM P
  INNER JOIN PC AS a ON P.Id=a.Id and a.Cod='A' and a.Val>100
  INNER JOIN PC AS b ON P.Id=b.Id and a.Cod='B' and a.Val>80

How can I implement SQL INTERSECT and MINUS operations in MS Access

INTERSECT is an inner join. MINUS is an outer join, where you choose only the records that don't exist in the other table.

---

INTERSECT

select distinct
  a.*
from
  a
  inner join b on a.id = b.id

---

MINUS

select distinct
  a.*
from
  a
  left outer join b on a.id = b.id
where
  b.id is null

If you edit your original question and post some sample data then an example can be given.

EDIT: Forgot to add in the distinct to the queries.

coalesce alternative in Access SQL

Access supports the Nz function and allows you to use it in a query. Note though that Nz is the same as the T-SQL ISNULL function. It can not take an arbitrary number of parameters like COALESCE can.

---

Related Topics