Filtering for "Not Something"

[Emma Hope]

I have one table which contains all my contacts i.e. fields ContactID & Name
and a second table containing tasks, i.e. fields ContactID & Task.

So for example:
Table1
ContactID Name
1 Fred Smith
2 Joe Bloggs
3 Mickey Mouse
4 Minnie Mouse
5 Donald Duck

Table2
ContactID Task
1 Sent Email
2 Sent Email
3 Telephoned

I want to run a query that shows me all those who have not had a particular
task, i.e. for the above example, ContactIDs 3, 4 & 5 have NOT been 'Sent
Email' but when i run a query joined one to many (arrow goes from Table1 to
Table2) from Table1 to Table2 (on ContactID) & filter for NOT "Sent Email", i
only get contactID 3 show up (i.e. the one that has a record on Table2) i
want ContactID 3,4 & 5 to show up.

Can anyone help please!
Thanks
Emma

 

[Bob Barrows [MVP]]

I have one table which contains all my contacts i.e. fields ContactID
& Name and a second table containing tasks, i.e. fields ContactID &
Task.

So for example:
Table1
ContactID Name
1 Fred Smith
2 Joe Bloggs
3 Mickey Mouse
4 Minnie Mouse
5 Donald Duck

Table2
ContactID Task
1 Sent Email
2 Sent Email
3 Telephoned

I want to run a query that shows me all those who have not had a
particular task, i.e. for the above example, ContactIDs 3, 4 & 5 have
NOT been 'Sent Email' but when i run a query joined one to many
(arrow goes from Table1 to Table2) from Table1 to Table2 (on
ContactID) & filter for NOT "Sent Email", i only get contactID 3 show
up (i.e. the one that has a record on Table2) i want ContactID 3,4 &
5 to show up.

Right-click the arrow joining the tables (it's tricky - persist until you
right-click exactly on that line) and choose Jon properties. Then click the
button that says something like "select all records from Table1 and only
records from Table2 that satisfy the join" and click OK. Run the query and
you should see all the records from Table1 now, with Null in the unmatched
Task field.

Congratulations, you've just created your first outer join. Up to now you've
been working with simple inner joins.

Now it's simply a matter of filtering out the matched ones. Go back to
Design View and in the Criteria row under Task, enter "Is Null" (without the
quotes, of course).

 

[Emma Hope]

Bob,

My post states i have already made the join, i apologise for not using the
correct terminology.

I am past this stage in my abilities and use joins regularly. If you make a
simple db & add the data i have given as my example, you will see the
problem, even with an inner join. With the inner join, i see ContactID 3 only
and not ContactID 4 & 5 which is what i need.

Any other ideas?
Emma

P.S. I have tried is null as well but this doesn't show what i need either.

 

[Rick Brandt]

Bob,

My post states i have already made the join, i apologise for not
using the correct terminology.

I am past this stage in my abilities and use joins regularly. If you
make a simple db & add the data i have given as my example, you will
see the problem, even with an inner join. With the inner join, i see
ContactID 3 only and not ContactID 4 & 5 which is what i need.

Any other ideas?
Emma

P.S. I have tried is null as well but this doesn't show what i need
either.

Have you tried...

WHERE ContactID Not In(SELECT ContactID FROM Table2 WHERE Task = "Sent
Email")

 

[John Spencer]

You can do this in two queries.

First Query - find every contact that HAS a record with Sent EMail as the task.

SELECT [Contact Id]
FROM Table2
WHERE Task = "Sent EMail"

Save that query as qGotEmail. Now use the Unmatched query wizard to find
records in table 1 that don't match records in the query. The query will look
something like the following.

SELECT Table1.*
FROM Table1 LEFT JOIN qGotMail
ON Table1.[Contact ID] = qGotMail.[Contact Id]
WHERE qGotMail.ContactID is Null

A one query method uses Not IN in the where clause. Not IN is slow with large
sets of records.

SELECT Table1.*
FROM Table1
WHERE [Contact ID] NOT IN
(
SELECT [Contact Id]
FROM Table2
WHERE Task = "Sent EMail")

In the query grid,
-- Add Table1
-- Add the fields you want to see
-- Under the contact id set the criteria as
NOT IN (SELECT [Contact Id] FROM [Table2] WHERE Task = "Sent EMail")


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Bob Barrows [MVP]]

Bob,

My post states i have already made the join, i apologise for not
using the correct terminology.

No, your initial post does not state so, but we will move on. The best
way to make sure we understand the query you are using is to show us the
sql statement being generated by your actions in Design View. Do this by
switching your query to SQL View (View|SQL View, or the toolbar button,
or the right-click context menu). If you had followed my instructions,
you should see an outer join, not an inner join.

I am past this stage in my abilities and use joins regularly. If you
make a simple db & add the data i have given as my example, you will
see the problem, even with an inner join. With the inner join, i see
ContactID 3 only and not ContactID 4 & 5 which is what i need.

Which is why I told you an outer join was needed. Here is how the sql to
retrieve your data should look: ... oh, my bad. I neglected to take into
account the task criterion. See John Spencer's reply.


..