not in

[seeker]

this query gives me an error in from clause

select [emp:macro] from employee where not in (select
[emp:macro] from employee2)

trying to compare two tables to see what records are
missing from the one.

seeker

 

[John Viescas]

SELECT *
FROM employee
LEFT JOIN employee2
ON employee.[emp:macro] = employee2.[emp:macro]
WHERE employee2.[emp:macro] IS NULL

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Douglas J. Steele]

John's shown you the better way to do what you're trying to do. I just
wanted to point out that why your query didn't work was because you forgot
to include a field name to use against the Not In statement:

SELECT [emp:macro]
FROM employee
WHERE [emp:macro] NOT IN (SELECT
[emp:macro] FROM employee2)