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

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

"Roger Carlson" wrote:

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


"scubadiver" wrote in message
...
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.


"David M C" wrote:

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!*

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


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

"Roger Carlson" wrote:

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


"scubadiver" wrote in message
...
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.


"David M C" wrote:

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!*

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?

"Allen Browne" wrote:

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"scubadiver" wrote in message
...
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.

"Allen Browne" wrote:

Which is the foreign key of this table.

Do you really have a foreign key called "Name"?
What happens when you have 2 employee with the same name?
Do you also have fields in this table for the address of each person?
So if someone attends 2 courses, you have to enter their address in 2
records?

"scubadiver" wrote in message
...
Simple example:

1:n relationship and create the following query.

Name Course

John A
John B
John C
Sarah A
Sarah C
Sarah D
Phil B
Phil C
Phil D

I now know that

Course A was attended by John and Sarah
Course B was attended by John and Phil
Course C was attended by John, Sarah and Phil
Course D was attended by Sarah and Phil

If there is anything wrong with this please let me know.

"Allen Browne" wrote:

Clearly, I have no idea what you are talking about either.

If your tables have no foreign keys, you can do what you like.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"scubadiver" wrote in message
...
But that doesn't mean to say I can't list all the employees who have
taken
a
specific course? If establishing a 1:n relationship between course
and
employee means it could be quicker to enter information into a form,
then
there is a trade off.

ok. Thanks.

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

"Roger Carlson" wrote:

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


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

"Roger Carlson" wrote:

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


"scubadiver" wrote in message
...
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.


"David M C" wrote:

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!*

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

"Roger Carlson" wrote:

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


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

"Roger Carlson" wrote:

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


"scubadiver" wrote in message
...
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.


"David M C" wrote:

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!*

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.

"scubadiver" wrote:

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?

"Allen Browne" wrote:

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"scubadiver" wrote in message
...
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.

"Allen Browne" wrote:

Which is the foreign key of this table.

Do you really have a foreign key called "Name"?
What happens when you have 2 employee with the same name?
Do you also have fields in this table for the address of each person?
So if someone attends 2 courses, you have to enter their address in 2
records?

"scubadiver" wrote in message
...
Simple example:

1:n relationship and create the following query.

Name Course

John A
John B
John C
Sarah A
Sarah C
Sarah D
Phil B
Phil C
Phil D

I now know that

Course A was attended by John and Sarah
Course B was attended by John and Phil
Course C was attended by John, Sarah and Phil
Course D was attended by Sarah and Phil

If there is anything wrong with this please let me know.

"Allen Browne" wrote:

Clearly, I have no idea what you are talking about either.

If your tables have no foreign keys, you can do what you like.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"scubadiver" wrote in message
...
But that doesn't mean to say I can't list all the employees who have
taken
a
specific course? If establishing a 1:n relationship between course
and
employee means it could be quicker to enter information into a form,
then
there is a trade off.

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



"scubadiver" wrote in message
news
So there is no great advantage except that you can enter information in
two
different ways?

"Roger Carlson" wrote:

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


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

"Roger Carlson" wrote:

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


"scubadiver" wrote in message
...
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.


"David M C" wrote:

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!*

"scubadiver" wrote in message
:
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 ;-)

--
Roy-Vidar

On Fri, 10 Nov 2006 09:12:01 -0800, scubadiver
wrote:

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]

On Fri, 10 Nov 2006 09:18:02 -0800, scubadiver
wrote:

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]