Exclude a record from query results

  • Thread starter Thread starter Ngan Bui
  • Start date Start date
N

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
 
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.
Click to expand...

I'd suggest building a SQL string with

WHERE Category IN(2, 3, 8) AND Category NOT IN(71)
 
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
 
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.
Click to expand...

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
 
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
 
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.
Click to expand...

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))
 
How did you come up with that code?
Click to expand...

Doing it a long time said:
Thought my logic was
simple enough to be translated to a simple code. guess
not.
Click to expand...
Actually, it is pretty simple once you know about the EXISTS keyword.

All the best


Tim F
 
Back
Top