Exclusion Queries

[Alf]

Hi, relatively new to access, so muddling my way through.

I have created a table, and firms are sorted by the sector they work in, I
need to create a query that allows me to pick one or more sectors, and then
see the firms that DO NOT fall into the selected sectors!

 

[[MVP] S.Clark]

Select * from tablename WHERE Sector NOT IN ("S1","S2","S3")

SELECT Products.ProductName, Categories.CategoryName
FROM Categories
INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID
WHERE (((Categories.CategoryName) Not In ("Beverages","Condiments")));

This is an example created in Northwind. It demonstrates one way to exclude
values. Notice the "Not In" in the WHERE clause.

To get this, you would have to use some VBA to create the values for the IN
clause. If you don't have that expertise, then try the following instead.

Create a new table to hold the select ID's. Then use that table in a query
to exclude the records. In the following example, the table ztblCatsSelect
holds the selected values.(On the form, use a combobox within a subform to
allow the user to easily select the values.)

SELECT Products.ProductName, Categories.CategoryName
FROM Categories
INNER JOIN (ztblCatsSelect RIGHT JOIN Products ON ztblCatsSelect.CategoryID
= Products.CategoryID) ON Categories.CategoryID = Products.CategoryID
WHERE (((ztblCatsSelect.CategoryID) Is Null));

 

[KARL DEWEY]

It seems like you need three tables -
Firms - identifies the organization
Sector - identifies the sector
Firm-Sector - has FirmID and SectorID

Firm-Sector is related in one-to-many from Firms and Sectors as a firm can
work in several sectors and a sector may be performed by many firms.

Then you can create a query on the joined Firms & Firm-Sector tables to find
those firms that are not involved in a given sector.