many-to-many relationships

[Cathy Solihull]

I am new to Access and trying to create a database that reflects all
employees / candidates with their relevant skill. I'm not sure how to go
about showing each employees skill as one skill can also be duplicated by
many employees.

Hope this makes sense. Please help!

 

[Jeff Boyce]

Cathy

The buzzwords/terms you'll want to use to look up more information include
"relational" and "normalization".

Access doesn't permit a direct "many-to-many" relationship. Instead, think
of a triangle -- two of the corners are your two entities (person; skill).
The third corner is a junction/relation/resolver table that holds two
fields: a rowID from Person and a rowID from Skill. This gives you the
valid combination of person & skill.

So if a particular person has 6 skills, you'd have six rows in this table
with the PersonID, each with a different SkillID.

Does that make sense?

Regards

Jeff Boyce
<Access MVP>

 

[BruceM]

Jeff,
I looked at this question for a while, then decided to see how somebody with
more experience than I answered it. Here is something I was pondering:
Assuming that skill can be summarized in a single word or a short phrase
(Carpentry, Plumbing, Landscape Design), if I was in a similar situation I
would consider having an EmployeeSkills table with three fields:
EmplSkillID, EmployeeID, and Description. There would be a one-to-many
between the EmployeeID fields. I would build a main form based on
tblEmployee and a continuous subform based on tblEmployeeSkills.
Description (in tblEmployeeSkills) would be populated by a combo box that
has another table as its row source, and Description as the bound (and only)
column. I would store the Description word or phrase (Carpentry, etc.)
rather than an ID in the Description field. My thinking is that if I am
going to store just one field it may as well be the word or phrase rather
than an ID.
I realize that if the term "auto mechanic" is replaced by "automotive
technician" in the list, then storing "Auto Mechanic" means the description
will remain in existing records even if the terminology changes. To prevent
that I could store the SkillID number, and have the description appear as
the visible column in the combo box, but in either case it would be a
one-to-many. If it takes two or more fields to describe the skill, the
values could be passed from the combo box row source to unbound text boxes.
If additional employee-specific information about the skill is needed (years
experience, for instance) then a many-to-many) would be necessary, but I
wonder if it is needed in a situation such as I described above. Even if it
is not necessary, is it advisable? I would be interested in hearing your
thoughts on this.

 

[Jeff Boyce]

Bruce

First, no guarantees of "more experience" -- the following are JOPO (just
one person's opinions)...

Jeff,
I looked at this question for a while, then decided to see how somebody with
more experience than I answered it. Here is something I was pondering:
Assuming that skill can be summarized in a single word or a short phrase
(Carpentry, Plumbing, Landscape Design), if I was in a similar situation I
would consider having an EmployeeSkills table with three fields:
EmplSkillID, EmployeeID, and Description. There would be a one-to-many
between the EmployeeID fields. I would build a main form based on
tblEmployee and a continuous subform based on tblEmployeeSkills.
Description (in tblEmployeeSkills) would be populated by a combo box that
has another table as its row source, and Description as the bound (and only)
column. I would store the Description word or phrase (Carpentry, etc.)
rather than an ID in the Description field. My thinking is that if I am

I would agree for something like (in the US) State, if I only ever needed to
store the state name. But if I were to be using both state name (e.g.,
Oregon) AND the state abbreviation (OR) (say, for addressing purposes), I'd
create a lookup table with state-related fields, and use whatever primary
key (probably the state abbreviation).

going to store just one field it may as well be the word or phrase rather
than an ID.
I realize that if the term "auto mechanic" is replaced by "automotive
technician" in the list, then storing "Auto Mechanic" means the description
will remain in existing records even if the terminology changes. To

prevent

That is exactly why I'd use a lookup table. If the description is changed,
I'm ... "efficient" (or lazy, depending on your point of view). I'd want to
only have to change the description once, not update an entire table of
embedded stored strings.

that I could store the SkillID number, and have the description appear as
the visible column in the combo box, but in either case it would be a
one-to-many. If it takes two or more fields to describe the skill, the
values could be passed from the combo box row source to unbound text boxes.
If additional employee-specific information about the skill is needed (years
experience, for instance) then a many-to-many) would be necessary, but I
wonder if it is needed in a situation such as I described above. Even if it
is not necessary, is it advisable? I would be interested in hearing your
thoughts on this.

The other aspect that might bear here (personal experience speaking) is if
the lookup values are date-bounded. For example, if a given skill was
deemed valid only between 1/1/2005 and 12/31/2005, I'd need/want to store
the ID that pertains to the valid date range (and be able to look that up
later).

Regards

Jeff Boyce

 

[Cathy Solihull]

Um, that sounds very jargony. I will however, try and find my way through.
Basically what I want is all my employees listed with their start dates,
email, etc. Then I want to list all their IT skills eg. Unix, RedHat,
Solaris, etc with information such as level of skill ie. basic, intermediate,
advanced. So obviously, one skill can relate to many employees and many
employees can have the same skill.

Does this make sense?

Thanks
Cathy

 

[Jeff Boyce]

Cathy

tblPerson (with person-only info, like name, phone number...)
PersonID
FirstName
LastName
...

tblSkill (with name/title of skill: e.g., Unix, RedHat, ...)
SkillID
SkillTitle
SkillDescription
???

trelPersonSkill (a "join/relation/resover" table, with facts about the
Person's Skill):
PersonSkillID
PersonID
SkillID
Level
DateCertified
...

(just an example, trying to model what your post described to me)

Regards

Jeff Boyce
<Access MVP>

 

[John Vinson]

Um, that sounds very jargony. I will however, try and find my way through.
Basically what I want is all my employees listed with their start dates,
email, etc. Then I want to list all their IT skills eg. Unix, RedHat,
Solaris, etc with information such as level of skill ie. basic, intermediate,
advanced. So obviously, one skill can relate to many employees and many
employees can have the same skill.

Does this make sense?

Short answer:

You need three tables.

People
PersonID
LastName
FirstName
<other biographical and employee data>

SkillList
Skill <text, Primary Key>
<maybe some other information about that skill>

Skills
PersonID <link to the People table>
Skill <link to the SkillList>
Level

The Primary Key of the Skills table would consist of the two fields
PersonID and Skill - in table design view, ctrl-click both fields and
the Key icon. This will prevent anomalies like having an employee with
Solaris skill entered twice.

If an employee has five skills, there would be five records in the
Skills table.

To enter data into this structure you could use a Form based on
People, with a subform based on Skills. On the subform you'ld have two
combo boxes, one based on SkillList to select the skill, and the other
(perhaps just a List of Values combo) for entering the level of skill.

John W. Vinson[MVP]

 

[BruceM]

Cathy,

I didn't mean to cloud the issue. I went off on a bit of a tangent to ask
Jeff a question about design in general. I know now that there is to be a
skill level listed next to each skill. Had I known that at the time I would
not have wondered about using a different approach than the one Jeff and
John have outlined in separate responses. This is not intended in any way
as a criticism of you for not asking quite the right question, for that too
is an acquired skill. You have explained your needs now, and are on your
way toward solving the problem. I didn't mean to muddy the waters with
terminology.

 

[TomHinkle]

I've read all the previous posts.. I believe all to be correct, simply
different ways of stating it.

I'll throw my hat into the ring.

When people talk about Many-to-Many, it's easy to represent conceptually (ie
on paper).

In practice, you always need 3 tables to represent a many to many
relationship.

Table A, Table B and a third table, that I like to call the intersedtion
table.

In your example, Table A could be employees, table B could be skills.

as another poster mentioned, the third table (intersection table) would
simply have valid combinations of keys from A & B.

in a sense you are doing a 1 - many from A to the intersection and a 1 -
many from B to the intersection.

All the intersection table needs is a field linked to the key of table A,
and table B. the final trick is to make the key of the intersection table a
multi field key.