Find customers with no email: Customers field? Email

[Guest]

I have merged a table of "lapsed customers" with a table of "all customers"
where just lapsed show with all the customer info, such as emails. There are
many contacts at one company. But what I need to find is only the companies
that have no contacts. Here's the dilemna sample

company a (e-mail address removed)
company a (e-mail address removed)
company b
company b (e-mail address removed)
company c
company d (e-mail address removed)

so I do I just get the company c because they have no email listed for any
contact? Help please. Thanks so much!!! Joni

 

[John Spencer]

SELECT Company
FROM SomeTable
WHERE NOT Exists
(SELECT *
FROM SomeTable as S
WHERE S.Company = SomeTable.Company
AND Email Is Not Null)



In the query grid add a calculated column and enter the following in the
field "cell"

Field: (SELECT * FROM [SomeTable] as S WHERE S.[Company] =
[SomeTable].[Company] AND Is Not Null)

Criteria: False

Of course, replace field and table names with your field and table names.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Marshall Barton]

I have merged a table of "lapsed customers" with a table of "all customers"
where just lapsed show with all the customer info, such as emails. There are
many contacts at one company. But what I need to find is only the companies
that have no contacts. Here's the dilemna sample

company a (e-mail address removed)
company a (e-mail address removed)
company b
company b (e-mail address removed)
company c
company d (e-mail address removed)

so I do I just get the company c because they have no email listed for any
contact?

Try something like:

SELECT Company
FROM thetable
GROUP BY Company
HAVING Count(emailaddress) = 0

 

[Guest]

thanks so much to both of you. This is perfect! It was probably simple to
you, but i'm VERY rusty on Access these days.

Joni