'NOT EXISTS' Query 1

[jt463]

I am having trouble pulling back the records I want.

I have two tables:
NewLotInformation (NLI) [primary key - subdivision]
WarrantyLetter (WL) [primary key - subdivision]

I want to pull back all records from NLI EXCEPT all the records that exist on WL. There are three records on WL that I want those subdivision records to be excluded from the query results.

The following is my SQL in Access:

SELECT DISTINCT NewLotInformation.Subdivision, NewLotInformation.DateClosed, NewLotInformation.Complete
FROM NewLotInformation
WHERE NOT EXISTS (SELECT *
FROM NewLotInformation, WarrantyLetter
WHERE [NewLotInformation].[Subdivision]=[WarrantyLetter].[Subdivision]);

I have tried some variants of the syntax, but every time I do, I get no results!

The following JOIN provides the results of the three records I want to EXCLUDE from my query:

SELECT NewLotInformation.Subdivision, NewLotInformation.Complete, NewLotInformation.DateClosed
FROM NewLotInformation INNER JOIN WarrantyLetter ON NewLotInformation.Subdivision = WarrantyLetter.Subdivision;

Thanks in advance!

 

[dhookom]

How about
SELECT DISTINCT NewLotInformation.Subdivision, NewLotInformation.DateClosed, NewLotInformation.Complete
FROM NewLotInformation
WHERE Subdivision Not In (SELECT Subdivision
FROM WarrantyLetter);


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jt463]

Genius!

Thanks Duane.

 

[PHV]

And if you prefer not use subquery (for performance reason):
SELECT N.Subdivision, N.Complete, N.DateClosed
FROM NewLotInformation As N LEFT JOIN WarrantyLetter As W ON N.Subdivision = W.Subdivision
WHERE W.Subdivision Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886