Handling a Many to Many Relationship

[Guest]

I have a data base to keep track of conferences that we sponsor, and (among
other things) of speakers at the conferences. Each conference can have many
speakers and each speaker can appear in many conferences. I have made a
joining table Conf-Spkr, with the primary key from the Conference Table and
the Primary Key from the Speaker table as the foreign keys. (I also tried
this except that I made the two foreign keys into a joint primary key in the
join table - this didn't work either). Two things: l) the join table does
not fill and 2) I am unable to write a query which shows both conferences and
speakers, so I assume I have the relationships wrong somewhere. I am not
experienced enough to know much "jargon" so please keep that in mind. Thanks.

 

[Guest]

You are on the right track...

I have made a joining table Conf-Spkr, with the primary key from the Conference > Table and the Primary Key from the Speaker table as the foreign keys.

That's right...

I made the two foreign keys into a joint primary key in the join table

Also right...

What form are you using to enter data?
Are you using a query as the RecordSource of your form or are you putting
the fields from your join table in a subform?

Steve

 

[Steve Schapel]

Ranger,

Perhaps this article may be of interest:
http://accesstips.datamanagementsolutions.biz/many.htm

It sounds like your problem #2 is a direct consequence of problem #1...
I mean, you won't be able to get a query to show speakers and
conferences unless there is data in the Conf-Spkr (or what I might call
Engagements) table. And your porblem #1 is likely related to how you
are entering the data... can you give some more details about what you
are doing with this?

 

[Guest]

When I first posted the question I was entering the data on two separate
forms: conference and speaker. After reading your response I made a query
using the two tables with no joins called ConferenceSpeaker and made two
forms: one called Speakers and a linking form called Conferences. The
Speaker form filled with the info I had already entered (23 speakers). The
conference form did not fill (two conferences), though the data was in the
Conference Table. I entered the data for the two conferences. The
Conference table now has four enteries, the two I entered yesterday and the
two I entered today on the new linking form. The Spkr-Conf joining table
filled with three speakers and no conference ID's.

I played around with putting a Spkr-Conf subform on the Speaker form but got
lost in the process. The subform did not fill with speakers and wouldn't let
me add any speakers that were not already in the table.

 

[Guest]

Thanks - I did read the article - and it confirms what I had learned in my
class - but somehow when I try to execute I find that I've missed a
connection somewhere. I do think the problem is probably in the data entry.
As I mentioned to SteveM above, I was entering data into two separate forms.
I have since developed a query between the two tables and made two linked
forms. Some of the data filled into the junction table, and some did not. I
am missing a link somewhere.
Thanks for trying to help.

 

[Steve Schapel]

Ranger,

In my experience, the most usual configuration for data entry is a main
form based on one of the "one-side" tables, and a subform based on the
"many-side" table.

So, you could have a main form based on your Conference table, and a
subform based on the Cnf-Spkr table,
*and/or*
a main form based on your Speakers table, and a subform based on the
Cnf-Spkr table.

In the first case, you would not need to show the ConferenceID field on
the subform, because that would be automatically taken care of via the
Link Master Fields and Link Child Fields properties of the subform. And
the SpeakerID would be entered in the sibform via a combobox, which is
set up such that its Row Source is based on the Speaker table, its Bound
Column is the SpeakerID (or whatever it's called), but the value
displayed in the combobox is the human-readable SpeakerName.

So then, you access the record for each Conference on the Conference
form, and you see listed in the subform all the Speakers for that
conference. And you could also access each speaker via the Speaker
form, and see in the subform a list of all the conferences that speaker
has participated in. Make sense?

 

[Guest]

Thank you - I added a subform (record source Conf-Spkr Table) in the main
speaker form (record source Speaker Table). The subform had two combo boxes:
speaker ID (which automatically filled when I added a new speaker in the
form) and Conference ID which I selected from the drop down box. This filled
the Conf-Spkr table. And that was good!!! To test this I added a Trial
Conference 1 and a Trial Conference 2 to the Conference Table and a Trial
Speaker (via the Speaker Form). I was only able to connect the Trial Speaker
to one conference at a time. When I selected Trial Conference 1 it filled in
correctly in the conf-Spkr table, but when I reopened the speaker form and
selected Trial Conference 2 it deselected Trial Conference 1. I need to be
able to keep a record of every conference that a speaker speaks at, without
reentering the speaker for each conference and ending up with lots of
duplicate speakers.
Thanks for your patience - I really appreciate your help.

 

[Steve Schapel]

Ranger,

You need to set up the form that you are using as the subform, as a
Continuous View form... so that you can enter/view more than one related
speaker record for the same conference. If you go to the design view of
the form you are using as the subform, you will see a Property called
Default View... change it to 'Continuous Forms'.

 

[Steve Schapel]

By the way, Ranger, you might as well also set the Visible property of
the Speaker ID control on the subform to No. It's a bit redundant in
that set up, as it will always show the same Speaker as on the main
form, so you don't need to see that on the subform as well.

 

[Guest]

Thank you!! I now have a table with multiple speakers and multiple
conferences. You were very helpful and I very much appreciate it.