Run a Query that Excludes Values in A Table

[DanCole42]

This seems like something that would be very simple, but I can't find
anywhere how to do it. I have a table that lists companies and their
addresses, and I want to run a query that excludes companies with
certain names. Ordinarily, I would just include something in the
Criteria portion of the query:

Not like "*microsoft*" and not like "*yahoo*" and not like "*disney*"
and not like "*morgan stanley*"

But there are over 2,000 companies I want to exclude. If I have the
companies I want to exclude in a table, how do I produce a query that
will exclude those 2,000 companies from my main table?

Thanks!

 

[adam.vogg]

if you have your 2 tables setup, one for all companies, one for
excluded companies,

you can use the Unmatched Query Wizard to find every record only in
the "all companies" table that doesnt match to the excluded table.

 

[DanCole42]

if you have your 2 tables setup, one for all companies, one for
excluded companies,

you can use the Unmatched Query Wizard to find every record only in
the "all companies" table that doesnt match to the excluded table.

I'll be running some pretty complex counts on the results of the
query, so I hope that will be sufficient.

How do I access the Unmatched Query Wizard? Thanks!

 

[Douglas J. Steele]

Your best bet would be to store the 2000+ company names in a table, and
write a query that joins the two tables.

Your query would be something like:

SELECT Table1.CompanyName, Table1.Field1, Table1.Field2
FROM Table1 LEFT JOIN ExcludeCompanies
ON Table1.CompanyName LIKE "*" & ExcludeCompanies.CompanyName & "*"
WHERE ExcludeCompanies.CompanyName IS NULL

 

[adam.vogg]

in access 2003, when you click the "New" button on the query tab, it
is one of the options that come up. not sure about other versions