How to display primary fields only once.

[DocBrown]

I have two tables where there is a one to many relation to the secondary
table. The secondary table also has a related lookup table that translates an
ID to a text field (to allow for an editable text list):

Primary - volunteers:
VolunteerID
First Name
Last Name
(etc)

Secondary - VolunteerFocus:
RecID
VolunteerID
TypeID

Lookup - FocusList:
TypeID
VolunteerFocus

I want to extract records where the selection can consist of multiple
VolunteerFocus. The following query gets the data I'm after. Eventually, the
'In' criteria will be derived from a form and substituted into the query.

SELECT Volunteers.*, VolunteerFocus.TypeID, FocusList.VolunteerFocus
FROM Volunteers INNER JOIN
(FocusList INNER JOIN VolunteerFocus ON
FocusList.TypeID = VolunteerFocus.TypeID)
ON Volunteers.VolunteerID = VolunteerFocus.VolunteerID
WHERE (((FocusList.VolunteerFocus) In ("birthday party","parent/child")));

If I display this as datasheet, and a volunteer has both of the focuses,
then I see multiple records:

Fred Thompson 1 'Birthday party'
Fred Thompson 2 'parent/child'
Susan Smith 1 'Birthday party'

I want to display this in a form like:

Fred Thompson Birthday Party
(Fred's data) parent/child

Susan Smith Birthday party
(Susan's data)

or maybe this way:

Birthday party: Fred Thompson
Susan Smith

parent/child: Fred Thompson

What is the approach to setup the form to do this? I assume I'll use sub
forms but I'm not sure how to reference the fields and link the sub forms to
do this. Any ideas are really appreciated.

John S.

 

[KARL DEWEY]

A form will not do it but a report or a form/subform will.
Volunteer in main form and VolunteerFocus in the subform linked
Master/Child on the VolunteerID.

 

[DocBrown]

I'm still missing something. I know I can create a report that does this by
creating section headers based on the desired fields.

How do you do this in a form/subform?

I created a form whose Record Source is the query below. I set the form to
Continuous so all the records were display in the detail section. But per my
example below, I get Fred Thompson displayed twice. AND continuous forms do
not allow subforms.

When set to Single Form, I still get the Volunteer fields twice. My main
form, which is strictly based on the Volunteers table works correctly with a
subform that displays the VolunteerFocus table linked on VolunteerID.

How can I construct a form based on the below query (or some other
equivalent method) that displays the data in a continuous form thus:

---------------------------------------
Fred Thompson Birthday Party
Parent/child
---------------------------------------
Susan Smith Birthday Party

Ultimately, I'd like to be able to allow the user to select some or all of
the displayed records where I will do further processing on the records
selected.

Or maybe I do something like:

Header:
Volunteer Focuses selected:
Birthday Party
Parent child
=================
Detail:
Fred Thompson (other data)
Susan Smith (other data)

Is this doable and how? Thanks for any ideas.
John S.