How to find when child table is missing an entry?

[wdsnews]

I'm stumped. In a flat file, you can test if the email field is empty. But
suppose your contact info is in a child table? How can I tell if a person
doesn't have an email address? The two tables are People and PhoneComm. In
PhoneComm, the entry is an email when [Contact ID]=5. Here is a query for
finding all the people WITH an email address....

SELECT People.[Last Name], People.[First Name], PhoneComm.PhoneComm AS Email
FROM People INNER JOIN PhoneComm ON People.ID = PhoneComm.[People ID]
WHERE (PhoneComm.[Contact ID])=5

How do I find people WITHOUT an email address?

....thanks for your help.

 

[George Hepworth]

Ther is a query wizard in Access to generate an "Unmatched Record" query
that should do exactly what you are asking for.

 

[Douglas J. Steele]

SELECT People.[Last Name], People.[First Name], PhoneComm.PhoneComm AS Email
FROM People LEFT JOIN PhoneComm ON People.ID = PhoneComm.[People ID]
WHERE PhoneComm.[Contact ID] IS NULL

 

[wdsnews]

I'll try it, but I don't think it will work because if they have a home
phone, or work phone, then [Contact ID] will not be null, it will just be
something other than 5.

SELECT People.[Last Name], People.[First Name], PhoneComm.PhoneComm AS
Email
FROM People LEFT JOIN PhoneComm ON People.ID = PhoneComm.[People ID]
WHERE PhoneComm.[Contact ID] IS NULL


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm stumped. In a flat file, you can test if the email field is empty.
But suppose your contact info is in a child table? How can I tell if a
person doesn't have an email address? The two tables are People and
PhoneComm. In PhoneComm, the entry is an email when [Contact ID]=5.
Here is a query for finding all the people WITH an email address....

SELECT People.[Last Name], People.[First Name], PhoneComm.PhoneComm AS
Email
FROM People INNER JOIN PhoneComm ON People.ID = PhoneComm.[People ID]
WHERE (PhoneComm.[Contact ID])=5

How do I find people WITHOUT an email address?

...thanks for your help.

 

[wdsnews]

Is that in v2007? I'm using Access 2003, and I can't find the "unmatched
record" query. The help system doesn't recognize it either.

thanks everyone for your suggestions.

Ther is a query wizard in Access to generate an "Unmatched Record" query
that should do exactly what you are asking for.


--
George Hepworth
2007 MS MVP in Access

I'm stumped. In a flat file, you can test if the email field is empty.
But suppose your contact info is in a child table? How can I tell if a
person doesn't have an email address? The two tables are People and
PhoneComm. In PhoneComm, the entry is an email when [Contact ID]=5.
Here is a query for finding all the people WITH an email address....

SELECT People.[Last Name], People.[First Name], PhoneComm.PhoneComm AS
Email
FROM People INNER JOIN PhoneComm ON People.ID = PhoneComm.[People ID]
WHERE (PhoneComm.[Contact ID])=5

How do I find people WITHOUT an email address?

...thanks for your help.

 

[wdsnews]

Ok. I solved it. Now I have two questions below. But, here's how I solved
it. First I created a query called "People Email Contact Query"...

SELECT People.ID, People.[Last Name], People.[First Name],
PhoneComm.PhoneComm AS Email
FROM People INNER JOIN PhoneComm ON People.ID = PhoneComm.[People ID]
WHERE (((PhoneComm.[Contact ID])=5));

Then I created a Query called "People WITHOUT Email Query"....

SELECT People.ID, People.[Last Name], People.[First Name], [People Email
Contact Query].Email
FROM People LEFT JOIN [People Email Contact Query] ON People.ID = [People
Email Contact Query].ID
WHERE ((([People Email Contact Query].Email) Is Null));

Is there a way to nest queries in a single query rather than creating two
separate queries? Will this two query technique work if I convert my Jet
project to SQL?

THANK YOU EVERYONE. You make me think and thinking is good!

 

[Douglas J. Steele]

It's not clear to me what you'd hope to acheive by combining the queries
into a single one. If you use LEFT JOIN like that but don't limit it to
those rows where Email is Null, your query will be give you those people
with Email addresses and those without.

I'm also very confused by your two queries. I would have expected you to be
using the same two tables in both, but you aren't.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok. I solved it. Now I have two questions below. But, here's how I
solved it. First I created a query called "People Email Contact Query"...

SELECT People.ID, People.[Last Name], People.[First Name],
PhoneComm.PhoneComm AS Email
FROM People INNER JOIN PhoneComm ON People.ID = PhoneComm.[People ID]
WHERE (((PhoneComm.[Contact ID])=5));

Then I created a Query called "People WITHOUT Email Query"....

SELECT People.ID, People.[Last Name], People.[First Name], [People Email
Contact Query].Email
FROM People LEFT JOIN [People Email Contact Query] ON People.ID = [People
Email Contact Query].ID
WHERE ((([People Email Contact Query].Email) Is Null));

Is there a way to nest queries in a single query rather than creating two
separate queries? Will this two query technique work if I convert my Jet
project to SQL?

THANK YOU EVERYONE. You make me think and thinking is good!

I'm stumped. In a flat file, you can test if the email field is empty.
But suppose your contact info is in a child table? How can I tell if a
person doesn't have an email address? The two tables are People and
PhoneComm. In PhoneComm, the entry is an email when [Contact ID]=5.
Here is a query for finding all the people WITH an email address....

SELECT People.[Last Name], People.[First Name], PhoneComm.PhoneComm AS
Email
FROM People INNER JOIN PhoneComm ON People.ID = PhoneComm.[People ID]
WHERE (PhoneComm.[Contact ID])=5

How do I find people WITHOUT an email address?

...thanks for your help.

 

[wdsnews]

In effect, the first query becomes the child table of the People table. The
first query reveals every PhoneComm child record which has an Email contact
type. That's what allows us to use 'null' in the second query to detect
people who don't have an email contact type in their child table. Otherwise
we simply get a list of people who don't have any contact info rather than
people who have contact info, but just not email contact info.

Do you know a different way to detect people who don't have email contact
info? (i.e. Contact Type=5)

It's not clear to me what you'd hope to acheive by combining the queries
into a single one. If you use LEFT JOIN like that but don't limit it to
those rows where Email is Null, your query will be give you those people
with Email addresses and those without.

I'm also very confused by your two queries. I would have expected you to
be using the same two tables in both, but you aren't.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok. I solved it. Now I have two questions below. But, here's how I
solved it. First I created a query called "People Email Contact
Query"...

SELECT People.ID, People.[Last Name], People.[First Name],
PhoneComm.PhoneComm AS Email
FROM People INNER JOIN PhoneComm ON People.ID = PhoneComm.[People ID]
WHERE (((PhoneComm.[Contact ID])=5));

Then I created a Query called "People WITHOUT Email Query"....

SELECT People.ID, People.[Last Name], People.[First Name], [People Email
Contact Query].Email
FROM People LEFT JOIN [People Email Contact Query] ON People.ID = [People
Email Contact Query].ID
WHERE ((([People Email Contact Query].Email) Is Null));

Is there a way to nest queries in a single query rather than creating two
separate queries? Will this two query technique work if I convert my Jet
project to SQL?

THANK YOU EVERYONE. You make me think and thinking is good!

I'm stumped. In a flat file, you can test if the email field is empty.
But suppose your contact info is in a child table? How can I tell if a
person doesn't have an email address? The two tables are People and
PhoneComm. In PhoneComm, the entry is an email when [Contact ID]=5.
Here is a query for finding all the people WITH an email address....

SELECT People.[Last Name], People.[First Name], PhoneComm.PhoneComm AS
Email
FROM People INNER JOIN PhoneComm ON People.ID = PhoneComm.[People ID]
WHERE (PhoneComm.[Contact ID])=5

How do I find people WITHOUT an email address?

...thanks for your help.

 

[John W. Vinson]

Do you know a different way to detect people who don't have email contact
info? (i.e. Contact Type=5)

A NOT EXISTS query will work:

SELECT People.ID, People.[Last Name], People.[First Name]
FROM People
WHERE NOT EXISTS (SELECT [PhoneComm].[People ID]
FROM PhoneComm
WHERE [People ID] = [People].[ID]
AND [Contact Type] = 5);

Don't expect this to run lightning fast...

John W. Vinson [MVP]