Association Difficulty

[Guest]

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.

 

[Jason Lepack]

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

 

[Guest]

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 (e-mail address removed)
12345 Johnson
12345 Smith
12345 Jones
12345 Wesson

67890 Jermaine (e-mail address removed)
67890 Jermaine
67890 Rodriguez
67890 Hun
67890 Doe
67890 Turtle

ABCD Plotz (e-mail address removed)
ABCD Plotz

Thank you for your help.