Find records in one table without relations in another

[rocketD]

Hello,

Ok, so I tried to normalize my database and now I have myself in a
pickle. I have two tables I'm trying to draw information from. One
is the main table with demographics and unique identifiers for
participants. The other is unique identifiers and records for each
survey they've taken. The relationship is one to many, so that one
participant can take many surveys.

What I need to know is how to set up a query that selects all records
in the main table that do NOT have a corresponding record in the
surveys table (e.g., the participant has never taken a survey).

This seems so basic but I'm stuck - please help!

Thanks,
Dara

 

[Arvin Meyer [MVP]]

There is a query wizard for that (Find UnMatched) just begin a new query and
select that as a choice. Follow the prompts.

 

[Gina Whipp]

Dara,

Look under Help for Unmatched query, that will give you what you want.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John Spencer]

The trick here is to construct a query that shows all the participants that
HAVE taken a specific survey. And then use that query along with the
participants table in an unmatched query - the Query wizard will build this
query for you once you have the first query built.

Another method that should work is the following, although with large data
sets it can be slower than some other methods.
SELECT Participants.*
FROM Participants as P
WHERE NOT Exists
(SELECT *
FROM Surveys
WHERE SurveyType = "Sports"
AND Surveys.ParticipantID = Participants.ParticipantID)

If you need help building the above because you only set up queries using the
query design view, post back for step by step instructions.

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