Exclude a record from query results

[Ngan Bui]

I have a table called tblContact with ContactID and
first/last name and such. Another table called
tblContactCategory with CCID, ContactID, and CategoryID
(from tblcategory).

This allows one contact to be in many categories.

On the report menu, I allow the user to choose more than
one category from a listbox by using the ctrl button to
choose. The command button will print a report of mailing
labels for those contacts that match up with those
categories.

The function to do that is:
strSql = "[CategoryID]="
For Each varItem In ctl.ItemsSelected
strSql = strSql & ctl.ItemData(varItem) & " OR
[CategoryID]="
Next varItem

'Trim out the extra [categoryid]= at the end of the SQL
strSql = Left$(strSql, Len(strSql) - 16)

'Open the report based on what category was chosen
DoCmd.OpenReport stDocName, acPreview, , strSql

So if a user choose category 2, 23, and 50, an example of
the SQL string would be: "[CategoryID] = 2 or [CategoryID]
= 23 or [CategoryID] = 50"

The report will just filter out the contacts.

The issue now is I want to exclude the contacts that have
the categoryID of 71 (they are on a listserv). So for
example, if I am on Category 2 but also on Category 71, I
wouldn't be listed in the report.

How can I do this?

Ngan

 

[John Vinson]

The issue now is I want to exclude the contacts that have
the categoryID of 71 (they are on a listserv). So for
example, if I am on Category 2 but also on Category 71, I
wouldn't be listed in the report.

I'd suggest building a SQL string with

WHERE Category IN(2, 3, 8) AND Category NOT IN(71)

 

[Ngan Bui]

I created a query with the tblContactCategory and listed
the ContactID and CategoryID.

In the criteria of the CategoryID, I typed in: "In (2, 3,
8) AND Not In (71)"

It still shows the contacts that have 71.

Am I doing something wrong? The tblContactCategory have
the following sample data:

PKID, ContactID, ContactCatID
1, 23, 2
2, 23, 5
3, 23, 71
4, 50, 12
5, 45, 8
6, 50, 3
7, 50, 71

With that, I should only see ContactID #45 because it's
the only one that has one of the three categories and does
not have category 71.

But the results I'm getting is more than that.

Thanks.
Ngan

 

[Tim Ferguson]

The issue now is I want to exclude the contacts that have
the categoryID of 71 (they are on a listserv). So for
example, if I am on Category 2 but also on Category 71, I
wouldn't be listed in the report.

You actually need a slightly more complex query than the one you have. It
goes along the lines of

SELECT ALL ContactNumber FROM Contacts
WHERE EXISTS
( SELECT * FROM ContactCategories AS a
WHERE a.ContactNumber = Contacts.ContactNumber
AND a.Category IN (2, 23, 50)
)
AND NOT EXISTS
( SELECT * FROM ContactCategories AS b
WHERE b.ContactNumber = Contacts.ContactNumber
AND b.Category IN (71)
)
ORDER BY ContactNumber ASC;

The use of "IN (71)" is obviously overkill for a single number, but it
makes the logic clearer here.

You can use a bit of string slicing to insert the different numbers you
want to filter on into the brackets.

Hope that helps


Tim F

 

[Ngan Bui]

Wow...that works...amazing how you come up with that
code. I was able to slice up the string to insert diff
catID.

How did you come up with that code? Thought my logic was
simple enough to be translated to a simple code. guess
not.

Thanks again!
Ngan

 

[John Vinson]

I created a query with the tblContactCategory and listed
the ContactID and CategoryID.

In the criteria of the CategoryID, I typed in: "In (2, 3,
8) AND Not In (71)"

It still shows the contacts that have 71.

ok... harder than I thought!

Try using a Query based on the Contact table, omitting
tblContactCategory from the query entirely. Use a criterion on
ContactID of

IN (SELECT ContactID FROM tblContactCategory WHERE CategoryID
IN(2,3,8)) AND NOT IN (SELECT ContactID FROM tblContactCategory WHERE
CategoryID IN(71))

 

[Tim Ferguson]

How did you come up with that code?

Thought my logic was
simple enough to be translated to a simple code. guess
not.

Actually, it is pretty simple once you know about the EXISTS keyword.

All the best


Tim F