Show Companies with no MainContacts

[iamnu]

CoName Table has a CompanyName field.
Contacts Table has the name of a Contact, and a MainContact field (yes/
no).
So the SQL statement would include CompanyName, Contact and
MainContact.

I want to display (list) those CompanyNames which do not have a
MainContact.

How do I make this calculation?

 

[Marshall Barton]

CoName Table has a CompanyName field.
Contacts Table has the name of a Contact, and a MainContact field (yes/
no).
So the SQL statement would include CompanyName, Contact and
MainContact.

I want to display (list) those CompanyNames which do not have a
MainContact.

Try using something like:

SELECT CompanyName
FROM CoName
WHERE Not Exist (SELECT 1
FROM Contacts
WHERE Contacts.CoID = CompanyName.CoID
And MainContact)

 

[iamnu]

Try using something like:

SELECT CompanyName
FROM CoName
WHERE Not Exist (SELECT 1
                                                                        FROM Contacts
                                                                        WHERE Contacts.CoID = CompanyName.CoID
                                                                                       And MainContact)

I'm getting a syntax error on "Not Exist". Did you mean something
else?

 

[Marshall Barton]

I'm getting a syntax error on "Not Exist". Did you mean something
else?

No, that's what I meant. Post your query.

 

[iamnu]

iamnuwrote:


No, that's what I meant.  Post your query.

Here is my query...

SELECT CoNameID
FROM tblAddresses
WHERE Not Exist ((((SELECT 1
FROM tblContacts
WHERE tblAddresses.AddrID = tblContacts.AddrID And MainContact))
=False));

 

[Marshall Barton]

SELECT CoNameID
FROM tblAddresses
WHERE Not Exist ((((SELECT 1
FROM tblContacts
WHERE tblAddresses.AddrID = tblContacts.AddrID And MainContact))
=False));

The parens are out of whack and the =False makes no sense.

If you can't get the query design view to generate SQL more
like what I posted, then skip the design view and do it all
in SQL view.