Many-to-Many Relationship

[Betsy M.]

My database is about educational programs. There is a general "program"
table that contains four fields about personnel who have roles in the program
(i.e., "Program Director," "Assoc. Program Director," "Program Coordinator,"
and "Assoc. Program Coordinator"). There is another "Program Personnel"
table that contains a record for each person who might fill one of the four
roles. Since an individual could be in one of the roles for one program and
fill another for another program, I think I have a "many-to-many"
relationship here and need to create a junction table in order to join the
tables so that I can produce the reports I want.

So, my first question is whether I am on the right track about needing to
create a many-to-many relationship using a junction table.

If so, I see the example of the "Order Details" table in datasheet view, but
what does it look like in design view? And then how are all of the tables
joined?

Any help appreciated.

 

[Jeff Boyce]

Yes. You use a junction table to resolve two tables in m:m into two pairs
of tables joined 1:m (the junction table goes in the middle, so you have 1:m
and m:1).

Datasheet views can be ... challenging. A common way to handle the data
entry/edit for a junction table is to use a main form (and a combobox) to
select one of your "1"s (say, to pick a person), then use a sub-form based
on the junction table to allow you to associate the other (?roles) that goes
with that person.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Betsy M.]

Thanks! Now I have a follow up question: I have populated the role-related
fields in the Program table with the record ID number of the designated
person from the Personnel table. So, is there additional data entry needed
in the junction table?

 

[Betsy M.]

Thanks. I'm still trying to think it through. Would TblProgramAdministrator
be the junction table?

 

[Jeff Boyce]

Betsy

You know your situation much better than we do.

If I were recording "person-in-role" data, I might also be interested in
FromDate and ToDate, since the same person could hold more than one role
over time, and the same role might be held by more than one person over
time.

Plus, if I recall, you mentioned that the roles were "common", in that more
than one business unit might have the same role. I'd think you'd want the
ID of the business unit also...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Betsy

Do you feel the responder has learned enough about your situation to be
telling you what you need?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

You might find it easier to visualise the model if, instead of thinking of
the relationship type as a many-to-many one, you think of it as a 3-way
relationship type (in the jargon its called a ternary relationship type).
What you have here are three 'referenced' entity types, Programs, Personnel
and Roles and a 3-way relationship type between them, which is modelled by a
fourth table.

It would also make things clearer I think if you name the tables Programs,
Personnel, Roles and ProgramPersonnel, the last being the table modelling the
relationship type between the other three, a so-called 'junction' table.
Using plural or collective nouns for table names reflects the fact that a
table is a set, and making them as close as possible to real English words
which describe the entity types makes it easier to 'read' the objects
(particularly when writing queries) . Giving their columns single nouns as
their names reflects the fact that each column represents an attribute of the
entity type which the table models (a relationship type is just a special
kind of entity type BTW, so the ProgramPersonnel table also models an entity
type, which in this case happens to be a relationship type).

The tables would thus be like this:

1. Personnel

PersonnelID (primary key)
FirstName
LastName
etc

This table is self explanatory I think. Note that a unique PersonnelID
surrogate key column is necessary as names can be duplicated and are
therefore unsuitable as keys.

2. Roles

Role (primary key)

This table would have four rows with values "Program Director" "Assoc.
Program Director" etc. More rows can be added later of course if necessary.
As each of these values is unique a numeric surrogate RoleID key is not
needed here, but you can use one if you wish.

3. Programs

Program (primary key)

Again as the program names will presumably be unique a ProgramID surrogate
key is not necessary, but can be used if you prefer. More columns
representing other attributes of the programs entity type would probably be
added.

4. ProgramPersonnel

PersonnelID
Role
Program

This table models the ternary relationship type between the other three
entity types. Each of the three columns is a foreign key referencing the
primary key of the other three tables. The primary key of this table is the
three columns in combination. If you do use 'natural' Role and Program keys,
in the relationships with the Roles and Programs tables, as well as enforcing
referential integrity you should also enforce cascade updates; this ensures
that if a value in the primary key column of Roles or Programs is changed the
values in all matching rows in ProgramPersonnel will also change.

For data entry a suitable set-up would be a programs form, based on a sorted
query on the programs table, and within this a program personnel subform
based on the ProgramPersonnel table, the subform being linked to the parent
form on the Program columns. The subform would best be in continuous form
view and would have two combo boxes, bound to the PersonnelID and Role
columns respectively. The latter simply needs a RowSource of:

SELECT Role FROM Roles ORDER BY Role;

The former should be set up to hide the bound PersonnelID column and show
the names, so its properties would be:

RowSource: SELECT PersonnelID, FirstName & " " & LastName FROM Personel
ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England

 

[Betsy M.]

No, I'm not sure, but I'm trying to think it through because I may have set
up my Program table incorrectly. Here's a synopsis:

TblProgram
ProgramID
ProgramName
Program Director ID
AssocPD ID
Coordinator ID
AssocCoord ID
<Other fields about programs>

TblProgramPersonnel
ProgramPersonnelID
<Contact fields for personnel>

I populated the four "role type" fields in the program table with the
ProgramPersonnelID numbers of the applicable individuals in the
ProgramPersonnel table. Are you wincing?

One of the products I was hoping to produce was a directory based on a
report or query that might have these columns:

ProgramName
Program director name
Program Director phone
Program Coordinator name
Program Coordinator phone
Program Coordinator rm #
Assoc Coordinator Name
Assoc coord phone
etc.

Someone who is a coordinator for one program might be the associate
coordinator for another program. Also, Coordinators and direcotrs may have
those roles in multiple programs, so I wanted to be able to use each of the
records in multiple capacities. Does Steve's solution allow for this?

 

[Jeff Boyce]

I'm crushed!

Was it something I said?

Jeff

Yes, look at it closely. In TblProgramAdministrator, for all programs
where you have data, each program is identified by ProgramID. ProgramID is
the primary key in TblProgram and ProgramID is the foreign key in
TblProgramAdministrator. Then for each program, all program administrators
are identified by ProgramPersonnelID. ProgramPersonnelID is the primary
key in TblProgramPersonnel
and ProgramPersonnelID is the foreign key in TblProgramAdministrator.
ProgramAdminTypeID in TblProgramAdministrator merely identifies what type
of administrator is each program administrator is.

Please ignore Boyce's totally ignorant comments. He is merely showing you
his disregard for what MVP is suppose to represent. His first response
provided no help and his second response only showed his ignorance!!

Steve

 

[John... Visio MVP]

Please ignore Boyce's totally ignorant comments. He is merely showing you
his disregard for what MVP is suppose to represent. His first response
provided no help and his second response only showed his ignorance!!

Steve

You are one to talk. You totally disregard the appropriate conduct and then
complain when someone rightfully if the OP had provided enough detail.

Your original response was just a feeble attempt to provide a pseudo answer
to use as leverage to get into your usual conjob of preying on unsuspecting
users.

These newsgroups are provided by Micorosft for FREE peer to support, not a
venue for you to ply your questionable services.

John...

 

[John... Visio MVP]

Betsy,

Study Ken's response. He's suggesting the same four tables I recommended.

To do the report you want, all you need to do is create a query that
includes the four tables Ken and I recommend.

Also ignore Marshall! He's another MVP that offers no help but likes to
show his total ignorance. He and Boyce make you wonder about what the MVP
program is actually about.

Steve

Calm down stevie. It looks like you are getting frustrated by people calling
you on your sleazy practise of pimping these newsgroups. You have been
pimping here for years so you must be quite familiar with the routine.

The only thing Jeff can be faulted for is making sure that the OP gets an
appropriate answer, something the MVPs are known for. As to my activity, you
keep me too busy hunting down your trash to provide the answers I have
provided in the past which have proven to be far better than what you have
posted. For now, I leave the posting of qualified answers to the resident
experts and MVPs (remember there are a lot of experts here who are NOT MVPS)
and I wil continue tying the bell around your neck until you do your annual
disappearing act.

Remember users; These newsgroups are provided by Microsoft for FREE peer to
peer support and there are many experts here, far more qualified than
stevie, who will gladly help for nothing more than a simple thank you.

John... Visio MVP

 

[Betsy M.]

Dear Ken, (and everyone else who has offered help - thank you, too)
I created the tables as you suggested. I opted to create ID fields in the
Program and Role tables, make them primary in their respective tables, and
used them in the junction table. Thus, the junction table has the three ID
fields from the entity-type tables, all of which are designated as primary.

I joined the tables using one to many relationships from the primary key
field of each of the three entity-type tables to the corresponding field in
the junction table.

I was able to create the sorted query and form for the program table, but
I'm stuck on the subform. Do I need to base this form on a query? I can't
seem to get any query that includes the junction table to work. If I include
the junction table in the query, I can't seem to include fields like
Lastname, firstname, and role. any suggestions?

I haven't gotten to the combo boxes yet. but your instructions were pretty
clear there

 

[John W. Vinson/MVP]

I was able to create the sorted query and form for the program table, but
I'm stuck on the subform. Do I need to base this form on a query? I can't
seem to get any query that includes the junction table to work. If I include
the junction table in the query, I can't seem to include fields like
Lastname, firstname, and role. any suggestions?

I haven't gotten to the combo boxes yet. but your instructions were pretty
clear there

That's your next step. It will solve the names problem.

The subform should be based on the junction table. Its recordsource
should contain the ID fields, but *nothing* from the other tables -
not lastname, not role, nothing!

Those will be displayed using the combo boxes. The Control Source of
the combo will be the ID field in the junction table; the RowSource
will be from (e.g.) the Roles table. The combo's ColumnCount,
ColumnWidths and BoundColumn properties would be set to *show* the
human-readable text but to *store* the numeric ID.

 

[Betsy M.]

Thank you John,
After sending off the request I decided to try working on the combo boxes,
and they worked! I may have some questions when the junction table is
complete. Thanks.