Association Difficulty

Windows XP Pro SP2, Access 2003

I have a query containing (among other fields) the fields: fldName (text),
fldID (number), fldPrimary (yes/no field), and fldEmail (text).

From this original query, I need to produce a query with the following
parameters:

If fldPrimary = yes, list the associated fldEmail, and all fldNames
(including the "yes" name) with the same fldID as the fldPrimary record (but
no fldEmail for the not-Primaries).

There are approximately 70 different fldIDs, and approximately 300 fldNames
and fldEmails, of which approximately 80 are fldPrimary = yes.

The results will be used as a database for a Word merge.

How do I set up the query? I've tried several options, but keep getting
stuck trying to associate all like fldIDs together with each other and
suppressing the not-primary fldEmails.

Any help or pointers much appreciated. Thank you.

The first step is to create a query that will list all of the ids
where "fldPrimary=yes".

Step 1:
SELECT fldID
FROM Allison
WHERE fldPrimary = Yes

Next you want to create a query that returns all fields that have the
same fldID as any record in Query1, or rather where the fldID in
Allison is "IN" query1

SELECT *
FROM Allison
WHERE fldID In (
SELECT fldID
FROM Allison
WHERE fldPrimary = Yes)

Cheers,
Jason Lepack

On Feb 14, 2:03 pm, Allison wrote:
Windows XP Pro SP2, Access 2003

I have a query containing (among other fields) the fields: fldName (text),
fldID (number), fldPrimary (yes/no field), and fldEmail (text).

From this original query, I need to produce a query with the following
parameters:

If fldPrimary = yes, list the associated fldEmail, and all fldNames
(including the "yes" name) with the same fldID as the fldPrimary record (but
no fldEmail for the not-Primaries).

There are approximately 70 different fldIDs, and approximately 300 fldNames
and fldEmails, of which approximately 80 are fldPrimary = yes.

The results will be used as a database for a Word merge.

How do I set up the query? I've tried several options, but keep getting
stuck trying to associate all like fldIDs together with each other and
suppressing the not-primary fldEmails.

Any help or pointers much appreciated. Thank you.

I think I'm missing something. Doing this nets me the same dataset I began
with.

Also, the fldName associated with the fldPrimary changes from time to time
when one person leaves, so I need to be able to do one step instead of two.

I need the end results to be similar to this (should be columns instead of
rows, but using rows for visual description) so it can be used in a Word
merge:

12345 Johnson
12345 Johnson
12345 Smith
12345 Jones
12345 Wesson

67890 Jermaine
67890 Jermaine
67890 Rodriguez
67890 Hun
67890 Doe
67890 Turtle

ABCD Plotz
ABCD Plotz

Thank you for your help.

"Jason Lepack" wrote:

The first step is to create a query that will list all of the ids
where "fldPrimary=yes".

Step 1:
SELECT fldID
FROM Allison
WHERE fldPrimary = Yes

Next you want to create a query that returns all fields that have the
same fldID as any record in Query1, or rather where the fldID in
Allison is "IN" query1

SELECT *
FROM Allison
WHERE fldID In (
SELECT fldID
FROM Allison
WHERE fldPrimary = Yes)

Cheers,
Jason Lepack

On Feb 14, 2:03 pm, Allison wrote:
Windows XP Pro SP2, Access 2003

I have a query containing (among other fields) the fields: fldName (text),
fldID (number), fldPrimary (yes/no field), and fldEmail (text).

From this original query, I need to produce a query with the following
parameters:

If fldPrimary = yes, list the associated fldEmail, and all fldNames
(including the "yes" name) with the same fldID as the fldPrimary record (but
no fldEmail for the not-Primaries).

There are approximately 70 different fldIDs, and approximately 300 fldNames
and fldEmails, of which approximately 80 are fldPrimary = yes.

The results will be used as a database for a Word merge.

How do I set up the query? I've tried several options, but keep getting
stuck trying to associate all like fldIDs together with each other and
suppressing the not-primary fldEmails.

Any help or pointers much appreciated. Thank you.