Changing Relationship from 1-to-1 to 1-to-Many

[Kay]

I have three tables set up.

1. Participant Contact Info - with their unique identifier (PartID)
as the primary key - not an autonumber.
2. Event Types - Event Type ID as the primary key. It is an
autonumber.
3. Events - enter the event information - Event ID is the primary key
- an autonumber.

In the form I made with the contact information, I have a command
button to open the Event form. I can enter one event for each person,
but it wont let me enter two or more events for the same person.

I am assuming this is because in my relationship table the link
between my Participant Contact Info table and Events table (linking
the two PartID fields) says one-to-one relationship. But I cant
figure out how to change it to one-to-many.

Does anyone know what I need to do or what I may be doing wrong?

Thank you,
K

 

[Kay]

I just noticed something else too. The event I enter for the first
contact is showing up as an event for every contact. Not good!

 

[tina]

the PartID field must be included in the Events table, as a foreign key. it
doesn't have to have to same name, but it must be the same Data Type (and if
a Number data type, it must be the same Field Size - Byte, Integer, Long
Integer, etc.). after adding the PartID field to the Events table, make sure
that the field's Index property is set to

Yes(Duplicates OK)

in the Relationships window, make sure that the relationship goes *from*
ParticipantContactInfo.PartID *to* Events.PartID (or whatever you named the
foreign key field). that should give you a one-to-many relationship with
ParticipantContactInfo on the one- side and Events on the -many side.

hth

 

[Kay]

Thats what I had done....and it wasnt working. So I deleted my PartID
in my events table and put it back in and it worked! Thank you!

 

[John W. Vinson]

I have three tables set up.

1. Participant Contact Info - with their unique identifier (PartID)
as the primary key - not an autonumber.
2. Event Types - Event Type ID as the primary key. It is an
autonumber.
3. Events - enter the event information - Event ID is the primary key
- an autonumber.

In the form I made with the contact information, I have a command
button to open the Event form. I can enter one event for each person,
but it wont let me enter two or more events for the same person.

I am assuming this is because in my relationship table the link
between my Participant Contact Info table and Events table (linking
the two PartID fields) says one-to-one relationship. But I cant
figure out how to change it to one-to-many.

You didn't mention a PartID field in the Events table - and there
certainly should NOT be one; that would mean that each Event could
have one and only one participant. You apparently have one, and it has
a unique ID - or maybe you're joining the PartID to the EventID!

Similarly, you probably should not have an EventID field in the
Participants table, because that would mean that each participant
would be limited to one and only one event.

You have a Many to Many relationship here: each Event can have many
Participants, and each Participant can participate in many events...
right?

Whenever you have a many to many relationship, you need *a new table*
- Participation, let's call it. It should have an EventID field (a
nonunique long integer foreign key to Events) and a PartID field (a
nonunique forign key to Participants), plus any fields needed to
describe THIS participant's involvement in THIS event; for example,
you might have a Role field for Speakers, Presenters, etc. This table
would have one record for each participant in each event; you'ld
typically use a Form based on the Events table with a subform based on
Participation, using a combo box to select participants. You may also
want to do it the other way - a Form based on Participants with a
subform based on Participation, showing which events they attended.

John W. Vinson [MVP]

 

[tina]

sounds like i didn't do anything, but you're welcome just the same.