Query Quandry

[xerxov]

I don't know Access jargin too well, so i'll try to explain this as
clearly as possible. I have a main table which contains the names of
all of the family members involved in a research project I work on.
Each family member has their own record. The table is keyed by an ID
number for each family member (IDNUM), and each family has an ID number
(FAMID) assigned to it as well so we can keep families together. So
everyone in the same family has the same family ID, and their own
unique ID.

In some families, everyone has been studied already. In some, only a
few of the people have been studied. And in some families we have been
unable to reach anyone. So here's where the problem arises...

I need to pull a list of everyone who is in a family where someone or
everyone has been studied. I pulled a list of names of people who have
been studied, and narrowed that table down to 1 record per FAMID, and
then did a query against the main table with eveyone in it with the
only criteria being "Like [studied]![FAMID]" (where studied is the
table of people who have been studied) in the FAMID field of the main
table.

What I get is only a list of the people in the Studied table. I don't
get any of the other people in the family that match that FAMID. I
have tried messing with keying the IDNUM and FAMID fields in the
studied table, and making different types of joins between the tables.
I always only get the people already in the studied table.

Does anyone know why this is happening? Need any other info? Is there
another way to use a field in another table as criteria in a query? I
don't even know what kinds of topics to look up for help on this
subject in the various Accesss manuals I have. In other databases,
its called an example element. What do they call this in access? It
seems to be to be among the most basic and simple types of functions of
a database, yet its so confusing and difficult in Access.... Can
anyone help... please??!!?!?!?

 

[Jeff Boyce]

To paraphrase (hopefully accurately)...

You want unique FamilyIDs from the tblStudied table -- this gives you all
Families with one/more family members studied.

You want to see all folks (persons, family members, ...) who share one of
the FamilyIDs that have at least one family member studied.

This may not be elegant, but I believe it solves your problem ... two
queries.

First query lists unique FamilyIDs in tblStudied.

Second query joins the first query back to the tblFamilyMembers on the
FamilyID, and displays all person names.

Or have I misunderstood?

Regards

Jeff Boyce
<Office/Access MVP>

 

[xerxov]

Thanks for your quick responce. I think we are saying the same thing...
which is quering out into a new table a list of everyone in the main
table who matches the FAMID criteria in the 'Studied' table; aka a list
of everyone in a family where anyone in that family has been studied.
But I'm not sure what you mean by 'joins the first query back to the
tblFamilyMembers on the FamilyID'. How do you join queries?

 

[Jeff Boyce]

Create a new query in design mode. Add a ... query as a source (use the "+"
toolbar button). Add the table. Join the two on the common field.

Regards

Jeff Boyce
<Office/Access MVP>

 

[xerxov]

k, i'll give that a shot. thanks!