Wanting to do a query that does not include records from a separate table

[Nanuaraq]

I've a table of companies and I want to query this table for certain characteristics.

I've also a list of companies that I don't want to appear on the query result. This list is in a separate table.



How to create a query that gives me results from the table of companies but leaves out those mentioned in the second table?

[ranman256]

make an outer join, in the query , bring in both tables,
join on Name. Dbl-click the join line.
set the property to ALL recs in tCompanies, SOME in tExclude.
bring down CONAME from both tables onto the grid,
under tExclued.CoName criteria set to IS NULL

this says, show all companies in tCompanies that are NOT in the tExclude list.

[Nanuaraq]

make an outer join, in the query , bring in both tables,
join on Name. Dbl-click the join line.
set the property to ALL recs in tCompanies, SOME in tExclude.
bring down CONAME from both tables onto the grid,
under tExclued.CoName criteria set to IS NULL

this says, show all companies in tCompanies that are NOT in the tExclude list.

Oooooh, that is so beautiful. And simple. I like simple.

[ArviLaanemets]

Another way (so long you have a single field in both tables to distinguish and identify companies):

SELECT * FROM tblCompanies WHERE CompanyID NOT IN (SELECT CompanyID from ExcludeList)

or

SELECT * FROM tblCompanies WHERE CompanyNameD NOT IN (SELECT CompanyName from ExcludeList)

[Nanuaraq]

Another way (so long you have a single field in both tables to distinguish and identify companies):

SELECT * FROM tblCompanies WHERE CompanyID NOT IN (SELECT CompanyID from ExcludeList)

or

SELECT * FROM tblCompanies WHERE CompanyNameD NOT IN (SELECT CompanyName from ExcludeList)

Thank you for this addition.