"Except" in Select Statement

[GLSmyth]

I need to select cells from one table that do not appear in a second table. I know that this can be done in some flavors of SQL by using Except:

Select Unit_PK From Table1
Except
Select Unit_PK From Table2

This would give me the Unit_PK cells in Table1 that do not exist in Table2.

Microsoft Access apparently does not allow Except or Minus in the SQL statement, so I am wondering if this is possible some other way. Your help is greatly appreciated.

Cheers -

george

[FishVal]

Hello, George.

The same could be done with outer join.

SELECT Table1.Unit_PK FROM Table1 LEFT JOIN Table2 ON Table1.Unit_PK=Table2.Unit_PK WHERE IsNull(Table2.Unit_PK);
 

[hjozinovic]

This is what you should do:
[HTML]Create Query based on Table1 and Table2.
Join between two tables should be set so that ALL records from one table are included and ONLY those from other table where the join fields are the same.
In your case Table1 has them all so it should include all the records from Table1 and only those from Table2 where the join fields are the same.
In WHERE row for the join field from Table2 put : IsNull[/HTML]

The query selects all the records from Table1 and matching records from Table2 leaving blanks in the field from Table2 where the values are missing.
After selecting only those records that are null in field from Table2 you get 'the difference'

This is my test query:

SELECT MainProfile.EmployeeID, LeaveApply.LaEmployeeID
FROM MainProfile LEFT JOIN LeaveApply ON MainProfile.EmployeeID = LeaveApply.LaEmployeeID
WHERE (((LeaveApply.LaEmployeeID) Is Null));

[GLSmyth]

IsNull is the key I was missing. Thank you very much for the comments.

Cheers -

george

[FishVal]

You are quite welcome.

Best regards,
Fish