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

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

"scubadiver" wrote:

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.

"David M C" wrote:

How??? The relationship you describe is:

tblEmployees:

EmployeeID (PK)
EmployeeName

tblCourses:

CourseID (PK)
CourseName
EmployeeID (FK)

Now tell me how many employees can be enrolled in the course with CourseID =
1? Where are you going to store all these extra EmployeeID's???

Dave

"scubadiver" wrote:

Not necessarily.

With just a 1:n relationship between employee and course not only can I
select multiple courses for one employee, I can also select the same course
for multiple employees.

"Roger Carlson" wrote:

Any 1:M relationship can be written in plain English in two sentences, one
for each direction. Like this:

Each Employee can take One or More Courses
Each Course can be taken by One And Only One Employee

This is what a One-To-Many relationship means, so by definition, if you
create a 1:M relationship, only one employee can take any given course.

--
--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/...UBED1=ACCESS-L


"scubadiver" wrote in message
...
...what I am missing?

If I have training courses and employees, I know that each employee
attends
many training courses and each course is attended by many employees. That
I
can understand.

If I set up a "1:n" relationship between "employee" and "course" I will
know
by DEFAULT who attended what course. Since I am assuming that this is the
purpose of having a "1:n" relationship between "course" and "employee"
doesn't this make the 2nd relationship completely redundant?

I could be entirely wrong ... *sigh!*