Many-2-many relationships: Can I be told ...

[David M C]

Yes, you can have as many employees as you like. But they can't each be
enrolled in the same course. You would have to create another course entry.

If you look at the table structure your model suggests you would have:

EmployeeID EmployeeName

1 Fred
2 Bob
3 Jim

CourseID CourseName EmployeeID

1 Jumping 1

Now, you can't have Jim also enrolled in "Jumping" because there is nowhere
to store his ID. Instead, you would have to create another "Jumping" course.

CourseID CourseName EmployeeID

1 Jumping 1
2 jumping 3

Now, tell me how you are going to create a query that shows me all the
employees that are enrolled in the Jumping course? Think of all the different
ways a user could type "Jumping" which you're going to have to allow for in
you query. Also remember that a course may have the same name, but be a
different course.

The whole point of using a relational database is to avoid duplication. By
having to enter all that course information again, just to have many
employees enrolled in it, is verging on insanity. Especially when creating a
junction table to model the data properly is so easy.

Dave

 

[kingston via AccessMonster.com]

If as David described, your data structure is:

tblEmployees:

EmployeeID (PK)
EmployeeName

tblCourses:

CourseID (PK)
CourseName
EmployeeID (FK)

You can have multiple employees take the same course only if CourseID is not
unique in tblCourses and you plan on duplicating course information. Hence,
tblCourses should have no employee information and you need a junction table
where CourseID and EmployeeID are both FK.

Now tell me how many employees can be enrolled in the course with CourseID =
1?

I can have as many employees as I like! Obviously it means duplication of
courses for each employee.

Where are you going to store all these extra EmployeeID's???

Each employee info is already stored in the main table.

How??? The relationship you describe is:

[quoted text clipped - 44 lines]

 

[Allen Browne]

So, once you put an EmployeeID into that field, the course is full?

You can have only one employee in the course?

That's what happens with a ONE to MANY relation.

 

[scubadiver]

Do you really have a foreign key called "Name"? No, I said it is a simple
example.

What happens when you have 2 employee with the same name? Each employee has
a unique payroll ID number.

The information for each employee is stored once.

 

[Roger Carlson]

Let me ask this, in your 1:n relationship, do you have Referential Integrity
enabled?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[scubadiver]

I will have a look up table for course type and name so there is no variation.

The bit I am struggling with is entering the information into a form. I have
created a simple example:

tble_course
CourseID (PK)
Crse_type
Crse_name

tble_detail
date
instructor
courseID (FK)
EmployeeID (FK)

tble_employee
EmployeeID (PK)
Empl_Name

Based on an example I have seen, I could have one of two:

(a)

In the main form I can have EmployeeID and Name
In the sub form I would have course type and name, date and instructor

(b)

In the main form I can have CourseID, type and Name
In the sub form I would have employee Name, date and instructor

At the moment my design is a 1:m and I have (a). So even if I have a m:m
relationship and I choose (a) I will still be entering the same course for
more than one employee. If I choose (b) I will be entering potentially the
same names for each course.

 

[scubadiver]

I most certainly do. How could I not?

Let me ask this, in your 1:n relationship, do you have Referential Integrity
enabled?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

What you just described IS a Many-to-Many relationship. You have a Course
table and an Employee table with the Detail table acting as the linking
table between them.

As for implementing it in a form, on my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"ImplementingM2MRelationship.mdb" which illustrates how to do this. There is
also another sample called "TrainingRegistration.mdb" which shows it being
used from both the Employee and Course perspective.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Allen Browne]

So, your table has:
- a PayrollID number (foreign key to Employee.EmployeeID)
- a CourseID (foreign key to Course.CourseID.)

Since you have *2* foreign keys in this table, it is a junction table
between 2 tables.

This junction table is the standard way of resolve a many-to-many relation
(between Employee and Course) into a pair of one-to-many relations (Employee
to the junction table, and Course to the junction table.)

 

[scubadiver]

I've got the "ImplementingM2MRelationship.mdb" and I have looked at it but
when it comes to entering information. Maybe it is a lot simpler than I think
it is but I cant get around the idea of dealing with two 1:m relationships
simultaneously because that is how it feels to me.

 

[scubadiver]

What is the difference between "frmDept" in your database and a form that has
been designed using a 1:m relationship?

 

[Roger Carlson]

The frmDept form IS built on a "logical" 1:M relationship. It is a
relationship between the table "DEPT" and the query "zqryItemTransaction".
(It is a "logical relationship" because no such physical relationship can
exist between a table and query.) The point is that zqryItemTransaction is a
Join of the other two tables (tblITEM and tblTRANS) in the M:M relationship.
And that's how you implement a M:M relationship in a form. You base the
main form one or the other of the main tables and base the subform on a Join
of the other main table and the linking table.

In the Training Registration database, I have two forms. 1) has the Student
in the main form and a Join of the linking table and Course table in the
subform and 2) the other has the Course in the main form and a join of the
other two in the subform. That way you can enter data from either
perspective.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[scubadiver]

This my current design:

tble_employee
EmployeeID (PK)
EmplName
workstatus
operations
Dept
Subdept
Supervisor

tble_course
employeeID
coursetype
coursename
trainingdate
instructor

I can see this is going nowhere because I still haven't been given a
practical answer as to why a m:m relationship is any better. Maybe it is a
lot simpler than I think it is but I can't see why.

To give an example, in "ImplementingM2MRelationship.mdb" one of the forms
consists of a main form (department) and a subform (transaction info and item
info). The fields are from two different tables, so why is this necessary?

I could be entering the same items for different departments? How is that
different to having 1:m relationship?

 

[scubadiver]

ok. Thanks.

Is it possible to use look up tables or combo boxes in a subform?

 

[scubadiver]

So there is no great advantage except that you can enter information in two
different ways?

 

[scubadiver]

Roger has told me how a m:m relationship is implemented in a form which I
wasn't aware of.

I have to say I still can't see any great advantage over a 1:m relationship
apart from the ability to enter information using one of two different tables.

 

[Roger Carlson]

I'm not really sure how to respond to this, because several of us have
already said it a number of ways. But the ONLY way you can represent your
data correctly is with a Many-to-Many relationship. And what you have
created (by your table design) IS a Many-to-Many relationship. There is NO
WAY you can use a One-to-Many relationship to accurately represent or store
your data. So yes, there is a great advantage, because a Many-to-Many is
the only one *possible*.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[RoyVidar]

Roger has told me how a m:m relationship is implemented in a form
which I wasn't aware of.

I have to say I still can't see any great advantage over a 1:m
relationship apart from the ability to enter information using one
of two different tables.

The advantage of using a M:N relationship over 1:M relationship for
what is actually a M:N relationship, is that the M:N relationship with
a junction table actually works. 1:N does not. Please, do try - you
will soon be able to confirm it.

This has to do with table design - how to store information - not form
design.

You need to finish your table design before even thinking about the
forms.

Several have tried to create a mutual ground on which to explain your
issues, here's another attempt http://r937.com/relational.html, see
"One-to-Many Relationships" vs "Many-to-Many Relationships" (though
the whole article is worth a read). You *DO* need the "Many-to-Many
Relationships" version, given your initial requirement!

"Two tables are related in a many-to-many (M—M) relationship when for
every row in the first table, there can be many rows in the second
table, and for every row in the second table, there can be many rows in
the first table. Many-to-many relationships can't be directly modeled
in relational database programs, including Microsoft Access. These
types of relationships must be broken into multiple one-to-many
relationships."

Failure to do so, will first and foremost haunt you, and make it
impossible for you to go to the pub with your collegues after regular
hours - you will have to stay in the office to do maintenance, cause
with such horrid structure, you will need everybody out of the db to
be able to perform maintenance. Needless to say, such maintenance
would be completely unnecessary with at good/correct design. You will
also have to spend §!$@#¤'ve a lot of hours explaining why your system
constantly gives wrong results, why it took ages to produce it in the
first place, and why it's extremely difficult to add new features to
it. But by all means, it's your job, and your reputation at stake, so
do what you like.

But, then you will probably come back and haunt us with more questions
about stuff that would never happen if you'd established a correct
database design in the first place ... cheers ;-)

 

[John Vinson]

tble_employee
EmployeeID (PK)
EmplName
workstatus
operations
Dept
Subdept
Supervisor

tble_course
employeeID
coursetype
coursename
trainingdate
instructor

I can see this is going nowhere because I still haven't been given a
practical answer as to why a m:m relationship is any better. Maybe it is a
lot simpler than I think it is but I can't see why.

To give an example, in "ImplementingM2MRelationship.mdb" one of the forms
consists of a main form (department) and a subform (transaction info and item
info). The fields are from two different tables, so why is this necessary?

I could be entering the same items for different departments? How is that
different to having 1:m relationship?

The problem with your design is that if a given course has 24
employees registered, you must have 24 records in tble_course for that
course. They all will have the same coursetype, coursename,
trainingdate, and instructor.

This wastes disk space, of course, since there is really only ONE
course; with a many to many, you would enter these fields once and
once only.

The *real* problem though is that if you have 24 records, there is no
way to be sure that the all DO in fact have the same coursename,
trainingdate, and instructor. You could have a data entry error
(typing the course date 24 times, wasting the data entry person's
time) where there's a typo, and you have 23 employees coming on 11/10
and one showing up on 11/11 wondering why there's nobody there. Or
your instructor might quit and need to be replaced; now you must track
down 24 records to change the instructor, whereas with the many to
many you make the change ONCE, in ONE record.

If you really want to store information redundantly, you can. You're
wasting storage, making your database less efficient, and - much worse
- risking the integrity of your data. If that cost is irrelevant to
you, go right ahead!

John W. Vinson[MVP]

 

[John Vinson]

Is it possible to use look up tables or combo boxes in a subform?

ummmm..

Of course. Possible, routine, universal.

Have you looked at the Northwind sample database Orders form? It's a
perfect example of a many to many (Orders analogous to Employees,
Products to Courses, OrderDetails the many to many relationship
table).

John W. Vinson[MVP]