Access Relationships

[Guest]

I have created a database with two tables consisting of programs and
participants. I am trying to create relationships that will allow the
Program ID # field in the Program Table to be related to the 4 program
options in the Participants Table. I have named the fields in the
Participants Table Program ID 1, Program ID 2, Program ID 3 and Program ID
4(which represent the 4 programs that the participant would like to attend.
I thought I would be able to go to the Participants Table and click the
subform allowing me to see the four programs the participant will be
attending. I have tried one-to-many, but it isn't working form me. Any
advice as to what I am doing wrong?

 

[Guest]

You should use a junction table to accomplish what you want.

tblPrograms
--------------
ProgramID
ProgramName
ProgramDate
etc...

tblParticipants
----------------
ParticipantID
ParticipantName
Phone
etc....

tblProgramChoices

 

[John Vinson]

I have created a database with two tables consisting of programs and
participants. I am trying to create relationships that will allow the
Program ID # field in the Program Table to be related to the 4 program
options in the Participants Table. I have named the fields in the
Participants Table Program ID 1, Program ID 2, Program ID 3 and Program ID
4(which represent the 4 programs that the participant would like to attend.
I thought I would be able to go to the Participants Table and click the
subform allowing me to see the four programs the participant will be
attending. I have tried one-to-many, but it isn't working form me. Any
advice as to what I am doing wrong?

What you're doing wrong is embedding a one (participant) to many
(program) relationship into every record of this table. "Fields are
expensive, records are cheap".

What you actually have is a classic Many (participants) to Many
(programs) relationship. The way such a relationship is modeled is
with THREE tables: Participants (primary key ParticipantID), Programs
(primary key ProgramID), and Participation:

Participation
ParticipentID ' who participated
ProgramID ' in what
<other information about this person's participation in this
program, if any>

Your subform would be based on this Participation table, using a combo
box bound to ProgramID to show which program the person was in.

John W. Vinson[MVP]