Excluding data from one query using another query

[sirace]

Hello everyone,

Here's my dilemma. I have two queries, "mailing_list_full" and "officer_search". The fields in "mailing_list_full" include account_no, address1, address2, address3. The fields in officer_search include account_no, address1, address2, address3. These two queries' data comes from the same table, only one (mailing_list_full) has 26120 records and the officer_search query only has 168 records. I want to create a 3rd query that shows all mailing addresses minus the accounts in the officer search. So far when I try and run this query, I get no results in my output. Here's my SQL:

SELECT mailing_list_full.*
FROM mailing_list_full LEFT JOIN officer_search ON mailing_list_full.account_no = officer_search.account_no
WHERE (((mailing_list_full.account_no)<>[officer_search]![account_no]));

Anyone have suggestions?

 

[PaulBricker]

You should be able to create this with the "Find Unmatched Query Wizard"

The sql looks like this

SELECT *
FROM [mailing_list_full] LEFT JOIN officer_search ON [mailing_list_full].[account_no] = [officer_search].[account_no]
WHERE ([officer_search].[account_no] Is Null);

Paul

 

[sirace]

Thanks alot for the speedy response! It worked beautifully, though I can't say I totally understand why. The concept makes sense (and obviously works) but some part of me is saying nothing should have been null... Oh well, won't tax my brain. Thanks again!