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