help

[djcamo]

Hi All,

I am using the contacts db that comes standard with MS Access. What I
am trying to do is create a query that gives me all the contacts that
have had no calls. I have no trouble finding all the ones that have
had a call, I just can't do the opposite. This is what I have so far
but it give nil results.

SELECT Contacts.CompanyName, Calls.CallID
FROM Contacts INNER JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE (((Contacts.Industry)<>"travel") AND ((Calls.CallID) Is Null));

Thanks in advance for any help

David.

 

[John W. Vinson]

I am using the contacts db that comes standard with MS Access. What I
am trying to do is create a query that gives me all the contacts that
have had no calls. I have no trouble finding all the ones that have
had a call, I just can't do the opposite. This is what I have so far
but it give nil results.

You need to use some pretzel logic here - something called a "Frustrated Outer
Join". Try

SELECT Contacts.CompanyName
FROM Contacts LEFT JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE (((Contacts.Industry)<>"travel") AND ((Calls.CallID) Is Null));

The LEFT JOIN returns all rows from Contacts, whether or not they have calls;
the CallID field will be NULL in those cases where there is no match. You were
using an INNER join (the default) which will only return Contacts which *do*
have calls, defeating the purpose!

John W. Vinson [MVP]

 

[djcamo]

You need to use some pretzel logic here - something called a "Frustrated Outer
Join". Try

SELECT Contacts.CompanyName
FROM Contacts LEFT JOIN Calls ON Contacts.ContactID = Calls.ContactID
WHERE (((Contacts.Industry)<>"travel") AND ((Calls.CallID) Is Null));

The LEFT JOIN returns all rows from Contacts, whether or not they have calls;
the CallID field will be NULL in those cases where there is no match. You were
using an INNER join (the default) which will only return Contacts which *do*
have calls, defeating the purpose!

John W. Vinson [MVP]

John, you are a legend. Thanks very much.