Training records

What fields are involved in the one-to-many relationship between tblEmployee
and tblCourse? If Johnny Jones takes the Basic Safety Practices course, you
store his ID in the Basic Safety Practices record in tblCourse? When Jill
Brown takes the same course, where do you store her ID? When you are
setting up the course, how do you store the attendance, in tblCourse or in
tblEmployee?
A principle of relational database design is that a table contains
information about a single entity such as Employee, Course, or Attendee.
Employee is not an attribute of a Course, nor is Course an attribute of an
Employee.
The thing I don't get here is that you described a pretty sound design, but
now you suddenly seem to be saying you don't want to do it that way.
Perhaps I don't understand the situation. Could you describe with some
pared-down examples what you mean when you say that a job title may have
many descriptions and vice versa. How do you populate a job description
with job titles? Do you have a certain number of fields for Job Titles in a
single Description record?

"scubadiver" wrote in message
...
Each training will happen when required.

At the moment, "tble_employee" and "tble_course" has a 1:n relationship. I
want to select an employee and populate it with the courses they attend.

But to give a different example I have created another database for my
dad.
He wanted a db to hold job titles and job descriptions. I decided to
design
the structure so that I would populate each job description with job
titles
because there are hundreds of job descriptions and each has lots of text
(it
would be too cumbersome)

I don't have a junction table even though each job title has many
descriptions and each description can be attached to many job titles. Even
with this design I can still create a query whereby I produce a report to
list all the descriptions for a particular job title.

I can't see why I can't do the same for the training database. I can
produce
a query that lists all the employees that have been on a given training
course.

cheers.

"BruceM" wrote:

Last question first: each employee may take many courses, and each
course
may be attended by many employees, so there is a many-to-many
relationship
between employees and courses; therefore, a junction table is needed to
resolve the relationship. Your original tblEmplCourse was the right
idea.
Your most recent one contains fields that are already in the course
table:

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

I would give the table a PK, and eliminate all or most of the non-FK
fields:

tblEmpCourse
EmpCourseID (PK)
EmployeeID (FK)
CourseID (FK)

You may want a comments field. If the course is on a particular date,
the
date field may not be necessary. Where I work the training may take
place
on different days for different employees (different shifts, etc.), but
there are training sessions as needed (for instance, a new process)
rather
than preset courses. You just need to make the best choice for your
situation. In any case, CourseType, CourseName, and Instructor are part
of
the Course record, so storing them in the junction table is probably
redundant. They are already part of the record identified by the
CourseID.

I'm not so much saying the CourseType table is not needed as I am asking
whether it is. If you store the CourseType in tblCourse or you have a
separate tblCourseType you can filter the Course records to see a listing
by
type or to provide the row source for a combo box. With a linked
CourseType
table a change to the name of a CourseType will be reflected in all
records
in which the CourseID was stored; if you store the name of the course
that
name will be preserved even if the CourseType is renamed. I am inclined
to
store the actual value in such a case, reasoning that I am storing a
value
in a single field whether that field contains an ID or the actual value,
but
you need to decide what is to happen if the value changes.

There are a number of approaches depending on the details, so I will put
a
few questions forward and wait for your responses. Is the idea that you
will select a Course record and then populate it with Employees, or the
other way around? Is a Course a one-time thing, or is it repeated from
time
to time? Where I work we have certain recurring training (First Aid,
etc.),
but rarely if ever is it the exact same training twice in a row, so I
decided to create each Session record as needed. Again, the details of
your
situation will affect how you proceed.

"scubadiver" wrote in message
...
thanks for the reply...

Are supervisors employees? "Yes"
Does a non-supervisor ever become a supervisor? On the side of caution,
"yes"
Do supervisors ever take courses? On the side of caution, "yes"
Yes/No field to designate them as supervisors: very good idea !!!

"an option group in tblCourse is one way of accomplishing what I assume
is
the need to group courses according to type". Not necessarily a need,
but
would be useful. There are LOTs of courses. Could use separate look up
tables
using a cascading combo?

So if I have you correctly, I could have just the following:

tble_course
CourseID (PK)
CourseType
CourseName

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

I am wondering whether I need three tables? Why not just two: employee
and
course.

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

tble_empcourse
EmployeeID (FK)
CourseType
CourseName
TrainingDate
Instructor

with two separate tables to act as course type and name look up tables?

cheers!

"BruceM" wrote:

Are supervisors employees? Does a non-supervisor ever become a
supervisor?
Do supervisors ever take courses? I repeat the earlier suggestion
that
supervisors and employees should be in the same table, with a Yes/No
field
to designate them as supervisors. This will let you query for all
records
in which that field is True, which presents the same information as a
Supervisor table, but with a fraction of the maintenance and other
hassles
that will surely result.
Also, Name should not be a PK field, and it should not be the name of
a
field since Name is a reserved word in Access. Perhaps it was just a
shortcut you used when posting the table structure, but I thought I'd
mention it anyhow.
Is SupID related to the PK field from tblSupervisor? I don't see a
SupID
field anywhere as a PK, so it's not entirely clear.
I'm not sure there's much point to having a CourseType table; an
option
group in tblCourse is one way of accomplishing what I assume is the
need
to
group courses according to type.
If the same course is offered twice, and is taught by a different
supervisor
each time, you will need to either add a new course record or modify
the
existing one. If you do the latter you won't have a record of who
taught
the course at any particular time. If you do the latter you will have
some
redundancy. You may want a table related one-to-many with the course
table
to store course dates and instructors, so that you can see when the
course
was taught and who taught it.
I would give all tables a PK field, including the junction table,
which
does
not show a PK field in your listing. You may not need it now, but it
gives
you flexibility going forward.
I urge you to reconsider using a separate Supervisor table if any of
the
conditions I mentioned could be true. The other observations and
questions
are things you may want to consider, depending on the details of your
situation. In general the structure looks pretty good.

"scubadiver" wrote in message
...
Thanks for the reply.

I don't have a great deal of understanding with databases of this
complexity
and this is a good time to learn. I have changed slightly what you
have
suggested. In the relationship table I have the following from left
to
right:

tble_coursetype
TypeID (PK)
TypeName

tble_supervisor
Name (PK)
Initials

tble_course
courseID (PK)
coursename
TypeID (FK)
SupID (FK)

tble_empcourse
EmployeeID (FK)
CourseID (FK)
TrainingDate

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept



Is it correct that it is better to deal with two forms at a time if
they
have a 1:n relationship? "tble_course" as above requires ID numbers
from
two
other separate tables. Having three separate tables makes it very
difficult
to understand how to enter the information into forms.

I am wondering whether Karl does have a point, though it makes sense
to
put
supervisor details in with "tble_course" rather than
"tble_employee".

Also, each job role will have its own list of training courses which
I
haven't established yet.





"tina" wrote:

suggest the following, as

tblSupervisors
SupID (primary key)
FirstName
LastName
Initials
i list initials as a separate field, because deriving initials
from
the
first/last names programmatically may lead to dups, ie. Mary Smith
and
Mark
Sanders.

tblCourseTypes
TypeID (primary key)
TypeName

tblCourses
CourseID (pk)
CourseName
TypeID (foreign key from tblCourseTypes)
SupID (fk from tblSupervisors)

tblDepartments
DeptID (pk)
DeptName

tblSubDepts
SubID (pk)
SubName
DeptID (fk from tblDepartments)

tblEmployees
EmpID (pk)
FirstName
LastName
Operations
Workstatus
SubID (fk from tblSubDepartments)

tblEmployeeCourses
EmpCourseID (pk)
EmpID (fk from tblEmployees)
CourseID (fk from tblCourses)
TrainingDate

relationships are
tblSupervisors.SupID 1:n tblCourses.SupID
tblCourseTypes.TypeID 1:n tblCourses.TypeID
tblDepartments.DeptID 1:n tblSubDepts.DeptID
tblSubDepts.SubID 1:n tblEmployees.SubID
tblEmployees.EmpID 1:n tblEmployeeCourses.EmpID
tblCourses.CourseID 1:n tblEmployeeCourses.CourseID

other tables suggest themselves (operations? workstatus?), and the
setup
may
well need to be tweaked upon further process analysis (remember
that
we
only
know what you tell us about the process you're trying to support
with
a
database), but the above should get you started. suggest you read
up
on
data
modeling; for further information, see
http://home.att.net/~california.db/tips.html#aTip1

hth


"scubadiver" wrote in
message
...
Anyone designed a db for this purpose?

At the moment I have in my mind the following logic:

For the first two tables in this list, each employee can attend
many

I seem to think that using a junction table confuses me so maybe I am
reluctant to use it because I am not sure how to use it for data entry
purposes.

How do you populate a job description with job titles? Do you have a
certain number of fields for Job Titles in a single Description record? When
I say "job description" maybe I should say "task descriptions" (when you
apply for a job you get a document with a list of tasks that go with the job).

The main form is for the job description and the subform is for the job
titles so I have as many titles as is required for that job description (1:n
relationship). For example, the job description may be, for simplicity,
"Write a report on a monthly basis", and I can attach all the relevant titles
that requires that description.

If I have 300 job descriptions and 20 job titles and each job description
has a number of titles attached as appropriate I can create a query that puts
all the fields together. I can then filter the records for a certain job
title and I will get a list of all the appropriate job descriptions. This
works fine even though the structure is the other way round.

In principle I can understand why a junction box should be used. In
practice, it makes form design and data entry difficult to understand, for me
anyway.

"BruceM" wrote:

What fields are involved in the one-to-many relationship between tblEmployee
and tblCourse? If Johnny Jones takes the Basic Safety Practices course, you
store his ID in the Basic Safety Practices record in tblCourse? When Jill
Brown takes the same course, where do you store her ID? When you are
setting up the course, how do you store the attendance, in tblCourse or in
tblEmployee?
A principle of relational database design is that a table contains
information about a single entity such as Employee, Course, or Attendee.
Employee is not an attribute of a Course, nor is Course an attribute of an
Employee.
The thing I don't get here is that you described a pretty sound design, but
now you suddenly seem to be saying you don't want to do it that way.
Perhaps I don't understand the situation. Could you describe with some
pared-down examples what you mean when you say that a job title may have
many descriptions and vice versa. How do you populate a job description
with job titles? Do you have a certain number of fields for Job Titles in a
single Description record?

"scubadiver" wrote in message
...
Each training will happen when required.

At the moment, "tble_employee" and "tble_course" has a 1:n relationship. I
want to select an employee and populate it with the courses they attend.

But to give a different example I have created another database for my
dad.
He wanted a db to hold job titles and job descriptions. I decided to
design
the structure so that I would populate each job description with job
titles
because there are hundreds of job descriptions and each has lots of text
(it
would be too cumbersome)

I don't have a junction table even though each job title has many
descriptions and each description can be attached to many job titles. Even
with this design I can still create a query whereby I produce a report to
list all the descriptions for a particular job title.

I can't see why I can't do the same for the training database. I can
produce
a query that lists all the employees that have been on a given training
course.

cheers.

"BruceM" wrote:

Last question first: each employee may take many courses, and each
course
may be attended by many employees, so there is a many-to-many
relationship
between employees and courses; therefore, a junction table is needed to
resolve the relationship. Your original tblEmplCourse was the right
idea.
Your most recent one contains fields that are already in the course
table:

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

I would give the table a PK, and eliminate all or most of the non-FK
fields:

tblEmpCourse
EmpCourseID (PK)
EmployeeID (FK)
CourseID (FK)

You may want a comments field. If the course is on a particular date,
the
date field may not be necessary. Where I work the training may take
place
on different days for different employees (different shifts, etc.), but
there are training sessions as needed (for instance, a new process)
rather
than preset courses. You just need to make the best choice for your
situation. In any case, CourseType, CourseName, and Instructor are part
of
the Course record, so storing them in the junction table is probably
redundant. They are already part of the record identified by the
CourseID.

I'm not so much saying the CourseType table is not needed as I am asking
whether it is. If you store the CourseType in tblCourse or you have a
separate tblCourseType you can filter the Course records to see a listing
by
type or to provide the row source for a combo box. With a linked
CourseType
table a change to the name of a CourseType will be reflected in all
records
in which the CourseID was stored; if you store the name of the course
that
name will be preserved even if the CourseType is renamed. I am inclined
to
store the actual value in such a case, reasoning that I am storing a
value
in a single field whether that field contains an ID or the actual value,
but
you need to decide what is to happen if the value changes.

There are a number of approaches depending on the details, so I will put
a
few questions forward and wait for your responses. Is the idea that you
will select a Course record and then populate it with Employees, or the
other way around? Is a Course a one-time thing, or is it repeated from
time
to time? Where I work we have certain recurring training (First Aid,
etc.),
but rarely if ever is it the exact same training twice in a row, so I
decided to create each Session record as needed. Again, the details of
your
situation will affect how you proceed.

"scubadiver" wrote in message
...
thanks for the reply...

Are supervisors employees? "Yes"
Does a non-supervisor ever become a supervisor? On the side of caution,
"yes"
Do supervisors ever take courses? On the side of caution, "yes"
Yes/No field to designate them as supervisors: very good idea !!!

"an option group in tblCourse is one way of accomplishing what I assume
is
the need to group courses according to type". Not necessarily a need,
but
would be useful. There are LOTs of courses. Could use separate look up
tables
using a cascading combo?

So if I have you correctly, I could have just the following:

tble_course
CourseID (PK)
CourseType
CourseName

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

I am wondering whether I need three tables? Why not just two: employee
and
course.

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

tble_empcourse
EmployeeID (FK)
CourseType
CourseName
TrainingDate
Instructor

with two separate tables to act as course type and name look up tables?

cheers!

"BruceM" wrote:

Are supervisors employees? Does a non-supervisor ever become a
supervisor?
Do supervisors ever take courses? I repeat the earlier suggestion
that
supervisors and employees should be in the same table, with a Yes/No
field
to designate them as supervisors. This will let you query for all
records
in which that field is True, which presents the same information as a
Supervisor table, but with a fraction of the maintenance and other
hassles
that will surely result.
Also, Name should not be a PK field, and it should not be the name of
a
field since Name is a reserved word in Access. Perhaps it was just a
shortcut you used when posting the table structure, but I thought I'd
mention it anyhow.
Is SupID related to the PK field from tblSupervisor? I don't see a
SupID
field anywhere as a PK, so it's not entirely clear.
I'm not sure there's much point to having a CourseType table; an
option
group in tblCourse is one way of accomplishing what I assume is the
need
to
group courses according to type.
If the same course is offered twice, and is taught by a different
supervisor
each time, you will need to either add a new course record or modify
the
existing one. If you do the latter you won't have a record of who
taught
the course at any particular time. If you do the latter you will have
some
redundancy. You may want a table related one-to-many with the course
table
to store course dates and instructors, so that you can see when the
course
was taught and who taught it.
I would give all tables a PK field, including the junction table,
which
does
not show a PK field in your listing. You may not need it now, but it
gives
you flexibility going forward.
I urge you to reconsider using a separate Supervisor table if any of
the
conditions I mentioned could be true. The other observations and
questions
are things you may want to consider, depending on the details of your
situation. In general the structure looks pretty good.

"scubadiver" wrote in message
...
Thanks for the reply.

I don't have a great deal of understanding with databases of this
complexity
and this is a good time to learn. I have changed slightly what you
have
suggested. In the relationship table I have the following from left
to
right:

tble_coursetype
TypeID (PK)
TypeName

tble_supervisor
Name (PK)
Initials

tble_course
courseID (PK)
coursename
TypeID (FK)
SupID (FK)

tble_empcourse
EmployeeID (FK)
CourseID (FK)
TrainingDate

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept



Is it correct that it is better to deal with two forms at a time if
they
have a 1:n relationship? "tble_course" as above requires ID numbers
from
two
other separate tables. Having three separate tables makes it very
difficult
to understand how to enter the information into forms.

I am wondering whether Karl does have a point, though it makes sense
to
put
supervisor details in with "tble_course" rather than
"tble_employee".

Also, each job role will have its own list of training courses which

The attendance would be be in "tble_course", not "tble_employee"

Going back to your first paragraph, I will apply your example.

I would go to Johnny's record and, in the subform, I would select the Basic
Safety Practices Course in the subform so that course is attached to his
name. I would then go to Jill Brown's record and do the same.

"BruceM" wrote:

What fields are involved in the one-to-many relationship between tblEmployee
and tblCourse? If Johnny Jones takes the Basic Safety Practices course, you
store his ID in the Basic Safety Practices record in tblCourse? When Jill
Brown takes the same course, where do you store her ID? When you are
setting up the course, how do you store the attendance, in tblCourse or in
tblEmployee?
A principle of relational database design is that a table contains
information about a single entity such as Employee, Course, or Attendee.
Employee is not an attribute of a Course, nor is Course an attribute of an
Employee.
The thing I don't get here is that you described a pretty sound design, but
now you suddenly seem to be saying you don't want to do it that way.
Perhaps I don't understand the situation. Could you describe with some
pared-down examples what you mean when you say that a job title may have
many descriptions and vice versa. How do you populate a job description
with job titles? Do you have a certain number of fields for Job Titles in a
single Description record?

"scubadiver" wrote in message
...
Each training will happen when required.

At the moment, "tble_employee" and "tble_course" has a 1:n relationship. I
want to select an employee and populate it with the courses they attend.

But to give a different example I have created another database for my
dad.
He wanted a db to hold job titles and job descriptions. I decided to
design
the structure so that I would populate each job description with job
titles
because there are hundreds of job descriptions and each has lots of text
(it
would be too cumbersome)

I don't have a junction table even though each job title has many
descriptions and each description can be attached to many job titles. Even
with this design I can still create a query whereby I produce a report to
list all the descriptions for a particular job title.

I can't see why I can't do the same for the training database. I can
produce
a query that lists all the employees that have been on a given training
course.

cheers.

"BruceM" wrote:

Last question first: each employee may take many courses, and each
course
may be attended by many employees, so there is a many-to-many
relationship
between employees and courses; therefore, a junction table is needed to
resolve the relationship. Your original tblEmplCourse was the right
idea.
Your most recent one contains fields that are already in the course
table:

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

I would give the table a PK, and eliminate all or most of the non-FK
fields:

tblEmpCourse
EmpCourseID (PK)
EmployeeID (FK)
CourseID (FK)

You may want a comments field. If the course is on a particular date,
the
date field may not be necessary. Where I work the training may take
place
on different days for different employees (different shifts, etc.), but
there are training sessions as needed (for instance, a new process)
rather
than preset courses. You just need to make the best choice for your
situation. In any case, CourseType, CourseName, and Instructor are part
of
the Course record, so storing them in the junction table is probably
redundant. They are already part of the record identified by the
CourseID.

I'm not so much saying the CourseType table is not needed as I am asking
whether it is. If you store the CourseType in tblCourse or you have a
separate tblCourseType you can filter the Course records to see a listing
by
type or to provide the row source for a combo box. With a linked
CourseType
table a change to the name of a CourseType will be reflected in all
records
in which the CourseID was stored; if you store the name of the course
that
name will be preserved even if the CourseType is renamed. I am inclined
to
store the actual value in such a case, reasoning that I am storing a
value
in a single field whether that field contains an ID or the actual value,
but
you need to decide what is to happen if the value changes.

There are a number of approaches depending on the details, so I will put
a
few questions forward and wait for your responses. Is the idea that you
will select a Course record and then populate it with Employees, or the
other way around? Is a Course a one-time thing, or is it repeated from
time
to time? Where I work we have certain recurring training (First Aid,
etc.),
but rarely if ever is it the exact same training twice in a row, so I
decided to create each Session record as needed. Again, the details of
your
situation will affect how you proceed.

"scubadiver" wrote in message
...
thanks for the reply...

Are supervisors employees? "Yes"
Does a non-supervisor ever become a supervisor? On the side of caution,
"yes"
Do supervisors ever take courses? On the side of caution, "yes"
Yes/No field to designate them as supervisors: very good idea !!!

"an option group in tblCourse is one way of accomplishing what I assume
is
the need to group courses according to type". Not necessarily a need,
but
would be useful. There are LOTs of courses. Could use separate look up
tables
using a cascading combo?

So if I have you correctly, I could have just the following:

tble_course
CourseID (PK)
CourseType
CourseName

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

I am wondering whether I need three tables? Why not just two: employee
and
course.

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

tble_empcourse
EmployeeID (FK)
CourseType
CourseName
TrainingDate
Instructor

with two separate tables to act as course type and name look up tables?

cheers!

"BruceM" wrote:

Are supervisors employees? Does a non-supervisor ever become a
supervisor?
Do supervisors ever take courses? I repeat the earlier suggestion
that
supervisors and employees should be in the same table, with a Yes/No
field
to designate them as supervisors. This will let you query for all
records
in which that field is True, which presents the same information as a
Supervisor table, but with a fraction of the maintenance and other
hassles
that will surely result.
Also, Name should not be a PK field, and it should not be the name of
a
field since Name is a reserved word in Access. Perhaps it was just a
shortcut you used when posting the table structure, but I thought I'd
mention it anyhow.
Is SupID related to the PK field from tblSupervisor? I don't see a
SupID
field anywhere as a PK, so it's not entirely clear.
I'm not sure there's much point to having a CourseType table; an
option
group in tblCourse is one way of accomplishing what I assume is the
need
to
group courses according to type.
If the same course is offered twice, and is taught by a different
supervisor
each time, you will need to either add a new course record or modify
the
existing one. If you do the latter you won't have a record of who
taught
the course at any particular time. If you do the latter you will have
some
redundancy. You may want a table related one-to-many with the course
table
to store course dates and instructors, so that you can see when the
course
was taught and who taught it.
I would give all tables a PK field, including the junction table,
which
does
not show a PK field in your listing. You may not need it now, but it
gives
you flexibility going forward.
I urge you to reconsider using a separate Supervisor table if any of
the
conditions I mentioned could be true. The other observations and
questions
are things you may want to consider, depending on the details of your
situation. In general the structure looks pretty good.

"scubadiver" wrote in message
...
Thanks for the reply.

I don't have a great deal of understanding with databases of this
complexity
and this is a good time to learn. I have changed slightly what you
have
suggested. In the relationship table I have the following from left
to
right:

tble_coursetype
TypeID (PK)
TypeName

tble_supervisor
Name (PK)
Initials

tble_course
courseID (PK)
coursename
TypeID (FK)
SupID (FK)

tble_empcourse
EmployeeID (FK)
CourseID (FK)
TrainingDate

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept



Is it correct that it is better to deal with two forms at a time if
they
have a 1:n relationship? "tble_course" as above requires ID numbers
from
two
other separate tables. Having three separate tables makes it very
difficult
to understand how to enter the information into forms.

I am wondering whether Karl does have a point, though it makes sense
to
put
supervisor details in with "tble_course" rather than
"tble_employee".

Also, each job role will have its own list of training courses which

Let's say that you want to populate the list of Responsibilities
(Descriptions) with job titles to which the Responsibility applies. In that
case you would use a JobTitle table, a Descriptions table, and a junction
table (let's call it tblDuties) between the two. There is a one-to-many
relationship between both tblResponsibilities and tblJobTitle and tblDuties:

tblResponsibilities
ResponsibilityID (PK)
RespDescription

tblJobTitle
JobTitleID (PK)
JobDescription

tblDuties (junction table)
DutyID (PK)
ResponsibilityID (FK)
JobTitleID (FK)

You could have other fields as well, depending on your needs. JobTitle may
include a ReportsTo field, or Department, or whatever.

In this scenario there is a one-to-many relationship between the PK fields
and the FK fields of the same name. That they have the same name here is
for convenience; it is not required. Remember that PK is defined in table
design view, but FK is defined only by its relationship to a PK field.

Once the relationships are defined it is time for the forms. Create a form
based on tblResponsibilites (frmResp) and another (fsubDuties) based on
tblDuties. Set the Default View on the property sheet for fsubDuties to
Continuous. Bind a combo box on fsubDuties to JobTitleID (it will be the
JobTitleID foreign key field from tblDuties). In the combo box property
sheet, click the Data tab, click Row Source, and click the three dots.
Select tblJobTitle, and add both fields (it would probably be a good idea to
sort by JobDescription while you're at it). Close the query, and update
when prompted. Still on the Data tab, set the bound column to 1 (assuming
JobTitleID is in the first column). Click the Format tab, and set the
Column Count to 2 and the column widths to 0";1" (or whatever you need for
the second column).
Save and close fsubJobTitle. Open frmResp in design view. Press F11 to
view the database window. Click the Forms tab, and drag the icon for
fsubJobTitle onto frmResp. Click View Properties (these are the
properties for the subform control, as the form you added is now called),
click the Data tab, and verify that ResponsibilityID is in the Link Parent
Field and Link Child Field rows. If it is not, click into one of the rows,
click the three dots, and follow the prompts and suggestions.
Switch to Form view. Your main form record is a Responsibility record, and
your subform may be used to list jobs to which that Responsibility applies.
In similar fashion you can build a main form based on tblJobTitle and a
subform based on tblDuties (the subform is always based on the junction
table), except that the combo box is now based on tblResponsibilites, and
the Link fields are JobTitleID. The same idea applies to a TrainingSession
main form and an Attendance subform, with the combo box on the subform
getting its Row Source from tblEmployee.
You can make a report / subreport in the same general way, except that the
subreport will be based on a query combining the second table (the one used
for the combo box row source) and the junction table. You can use the
report's Sorting and Grouping to arrange the data as you choose.
In general, one of the tables in a relationship including a junction table
is a relatively static table. In the first example (with the JobDescription
main form) the JobTitles are what I am calling "static", or less likely to
need updating. If you do need to update or add a JobTitle, you will need a
separate form based on tblJobTitle for that task. Once a JobTitle is added
to tblJobTitle it will be available as a selection from the combo box.
Records can be added as you go using the main form, but not so with the
table being used for the combo box Row Source.
There are all kinds of variations, but this is the basic idea of a setup
when there is a many-to-many (junction table) structure. I had a difficult
time at first grasping many-to-many relationships, but once I got them my
thinking about database design underwent a very, very important
transformation.

"scubadiver" wrote in message
...
I seem to think that using a junction table confuses me so maybe I am
reluctant to use it because I am not sure how to use it for data entry
purposes.

How do you populate a job description with job titles? Do you have a
certain number of fields for Job Titles in a single Description record?
When
I say "job description" maybe I should say "task descriptions" (when you
apply for a job you get a document with a list of tasks that go with the
job).

The main form is for the job description and the subform is for the job
titles so I have as many titles as is required for that job description
(1:n
relationship). For example, the job description may be, for simplicity,
"Write a report on a monthly basis", and I can attach all the relevant
titles
that requires that description.

If I have 300 job descriptions and 20 job titles and each job description
has a number of titles attached as appropriate I can create a query that
puts
all the fields together. I can then filter the records for a certain job
title and I will get a list of all the appropriate job descriptions. This
works fine even though the structure is the other way round.

In principle I can understand why a junction box should be used. In
practice, it makes form design and data entry difficult to understand, for
me
anyway.

"BruceM" wrote:

What fields are involved in the one-to-many relationship between
tblEmployee
and tblCourse? If Johnny Jones takes the Basic Safety Practices course,
you
store his ID in the Basic Safety Practices record in tblCourse? When
Jill
Brown takes the same course, where do you store her ID? When you are
setting up the course, how do you store the attendance, in tblCourse or
in
tblEmployee?
A principle of relational database design is that a table contains
information about a single entity such as Employee, Course, or Attendee.
Employee is not an attribute of a Course, nor is Course an attribute of
an
Employee.
The thing I don't get here is that you described a pretty sound design,
but
now you suddenly seem to be saying you don't want to do it that way.
Perhaps I don't understand the situation. Could you describe with some
pared-down examples what you mean when you say that a job title may have
many descriptions and vice versa. How do you populate a job description
with job titles? Do you have a certain number of fields for Job Titles
in a
single Description record?

"scubadiver" wrote in message
...
Each training will happen when required.

At the moment, "tble_employee" and "tble_course" has a 1:n
relationship. I
want to select an employee and populate it with the courses they
attend.

But to give a different example I have created another database for my
dad.
He wanted a db to hold job titles and job descriptions. I decided to
design
the structure so that I would populate each job description with job
titles
because there are hundreds of job descriptions and each has lots of
text
(it
would be too cumbersome)

I don't have a junction table even though each job title has many
descriptions and each description can be attached to many job titles.
Even
with this design I can still create a query whereby I produce a report
to
list all the descriptions for a particular job title.

I can't see why I can't do the same for the training database. I can
produce
a query that lists all the employees that have been on a given training
course.

cheers.

"BruceM" wrote:

Last question first: each employee may take many courses, and each
course
may be attended by many employees, so there is a many-to-many
relationship
between employees and courses; therefore, a junction table is needed
to
resolve the relationship. Your original tblEmplCourse was the right
idea.
Your most recent one contains fields that are already in the course
table:

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

I would give the table a PK, and eliminate all or most of the non-FK
fields:

tblEmpCourse
EmpCourseID (PK)
EmployeeID (FK)
CourseID (FK)

You may want a comments field. If the course is on a particular date,
the
date field may not be necessary. Where I work the training may take
place
on different days for different employees (different shifts, etc.),
but
there are training sessions as needed (for instance, a new process)
rather
than preset courses. You just need to make the best choice for your
situation. In any case, CourseType, CourseName, and Instructor are
part
of
the Course record, so storing them in the junction table is probably
redundant. They are already part of the record identified by the
CourseID.

I'm not so much saying the CourseType table is not needed as I am
asking
whether it is. If you store the CourseType in tblCourse or you have a
separate tblCourseType you can filter the Course records to see a
listing
by
type or to provide the row source for a combo box. With a linked
CourseType
table a change to the name of a CourseType will be reflected in all
records
in which the CourseID was stored; if you store the name of the course
that
name will be preserved even if the CourseType is renamed. I am
inclined
to
store the actual value in such a case, reasoning that I am storing a
value
in a single field whether that field contains an ID or the actual
value,
but
you need to decide what is to happen if the value changes.

There are a number of approaches depending on the details, so I will
put
a
few questions forward and wait for your responses. Is the idea that
you
will select a Course record and then populate it with Employees, or
the
other way around? Is a Course a one-time thing, or is it repeated
from
time
to time? Where I work we have certain recurring training (First Aid,
etc.),
but rarely if ever is it the exact same training twice in a row, so I
decided to create each Session record as needed. Again, the details
of
your
situation will affect how you proceed.

"scubadiver" wrote in message
...
thanks for the reply...

Are supervisors employees? "Yes"
Does a non-supervisor ever become a supervisor? On the side of
caution,
"yes"
Do supervisors ever take courses? On the side of caution, "yes"
Yes/No field to designate them as supervisors: very good idea !!!

"an option group in tblCourse is one way of accomplishing what I
assume
is
the need to group courses according to type". Not necessarily a
need,
but
would be useful. There are LOTs of courses. Could use separate look
up
tables
using a cascading combo?

So if I have you correctly, I could have just the following:

tble_course
CourseID (PK)
CourseType
CourseName

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

I am wondering whether I need three tables? Why not just two:
employee
and
course.

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

tble_empcourse
EmployeeID (FK)
CourseType
CourseName
TrainingDate
Instructor

with two separate tables to act as course type and name look up
tables?

cheers!

"BruceM" wrote:

Are supervisors employees? Does a non-supervisor ever become a
supervisor?
Do supervisors ever take courses? I repeat the earlier suggestion
that
supervisors and employees should be in the same table, with a
Yes/No
field
to designate them as supervisors. This will let you query for all
records
in which that field is True, which presents the same information as
a
Supervisor table, but with a fraction of the maintenance and other
hassles
that will surely result.
Also, Name should not be a PK field, and it should not be the name
of
a
field since Name is a reserved word in Access. Perhaps it was just
a
shortcut you used when posting the table structure, but I thought
I'd
mention it anyhow.
Is SupID related to the PK field from tblSupervisor? I don't see a
SupID
field anywhere as a PK, so it's not entirely clear.
I'm not sure there's much point to having a CourseType table; an
option
group in tblCourse is one way of accomplishing what I assume is the
need
to
group courses according to type.
If the same course is offered twice, and is taught by a different
supervisor
each time, you will need to either add a new course record or
modify
the
existing one. If you do the latter you won't have a record of who
taught
the course at any particular time. If you do the latter you will
have
some
redundancy. You may want a table related one-to-many with the
course
table
to store course dates and instructors, so that you can see when the
course
was taught and who taught it.
I would give all tables a PK field, including the junction table,
which
does
not show a PK field in your listing. You may not need it now, but
it
gives
you flexibility going forward.
I urge you to reconsider using a separate Supervisor table if any
of
the
conditions I mentioned could be true. The other observations and
questions
are things you may want to consider, depending on the details of
your
situation. In general the structure looks pretty good.

"scubadiver" wrote in
message
...
Thanks for the reply.

I don't have a great deal of understanding with databases of this
complexity
and this is a good time to learn. I have changed slightly what
you
have
suggested. In the relationship table I have the following from
left
to
right:

tble_coursetype
TypeID (PK)
TypeName

tble_supervisor
Name (PK)
Initials

tble_course
courseID (PK)
coursename
TypeID (FK)
SupID (FK)

tble_empcourse
EmployeeID (FK)
CourseID (FK)
TrainingDate

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept



Is it correct that it is better to deal with two forms at a time
if
they
have a 1:n relationship? "tble_course" as above requires ID
numbers
from
two
other separate tables. Having three separate tables makes it very
difficult
to understand how to enter the information into forms.

I am wondering whether Karl does have a point, though it makes
sense
to
put
supervisor details in with "tble_course" rather than
"tble_employee".

Also, each job role will have its own list of training courses
which

There is no good way of storing attendance in the course record. If the
Course is Basic Safety Practices, in what field would you add Johnny's
information? How about Jill's, or the hundredth person to take the course?
The junction table is essential.

"scubadiver" wrote in message
...
The attendance would be be in "tble_course", not "tble_employee"

Going back to your first paragraph, I will apply your example.

I would go to Johnny's record and, in the subform, I would select the
Basic
Safety Practices Course in the subform so that course is attached to his
name. I would then go to Jill Brown's record and do the same.

"BruceM" wrote:

What fields are involved in the one-to-many relationship between
tblEmployee
and tblCourse? If Johnny Jones takes the Basic Safety Practices course,
you
store his ID in the Basic Safety Practices record in tblCourse? When
Jill
Brown takes the same course, where do you store her ID? When you are
setting up the course, how do you store the attendance, in tblCourse or
in
tblEmployee?
A principle of relational database design is that a table contains
information about a single entity such as Employee, Course, or Attendee.
Employee is not an attribute of a Course, nor is Course an attribute of
an
Employee.
The thing I don't get here is that you described a pretty sound design,
but
now you suddenly seem to be saying you don't want to do it that way.
Perhaps I don't understand the situation. Could you describe with some
pared-down examples what you mean when you say that a job title may have
many descriptions and vice versa. How do you populate a job description
with job titles? Do you have a certain number of fields for Job Titles
in a
single Description record?

"scubadiver" wrote in message
...
Each training will happen when required.

At the moment, "tble_employee" and "tble_course" has a 1:n
relationship. I
want to select an employee and populate it with the courses they
attend.

But to give a different example I have created another database for my
dad.
He wanted a db to hold job titles and job descriptions. I decided to
design
the structure so that I would populate each job description with job
titles
because there are hundreds of job descriptions and each has lots of
text
(it
would be too cumbersome)

I don't have a junction table even though each job title has many
descriptions and each description can be attached to many job titles.
Even
with this design I can still create a query whereby I produce a report
to
list all the descriptions for a particular job title.

I can't see why I can't do the same for the training database. I can
produce
a query that lists all the employees that have been on a given training
course.

cheers.

"BruceM" wrote:

Last question first: each employee may take many courses, and each
course
may be attended by many employees, so there is a many-to-many
relationship
between employees and courses; therefore, a junction table is needed
to
resolve the relationship. Your original tblEmplCourse was the right
idea.
Your most recent one contains fields that are already in the course
table:

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

I would give the table a PK, and eliminate all or most of the non-FK
fields:

tblEmpCourse
EmpCourseID (PK)
EmployeeID (FK)
CourseID (FK)

You may want a comments field. If the course is on a particular date,
the
date field may not be necessary. Where I work the training may take
place
on different days for different employees (different shifts, etc.),
but
there are training sessions as needed (for instance, a new process)
rather
than preset courses. You just need to make the best choice for your
situation. In any case, CourseType, CourseName, and Instructor are
part
of
the Course record, so storing them in the junction table is probably
redundant. They are already part of the record identified by the
CourseID.

I'm not so much saying the CourseType table is not needed as I am
asking
whether it is. If you store the CourseType in tblCourse or you have a
separate tblCourseType you can filter the Course records to see a
listing
by
type or to provide the row source for a combo box. With a linked
CourseType
table a change to the name of a CourseType will be reflected in all
records
in which the CourseID was stored; if you store the name of the course
that
name will be preserved even if the CourseType is renamed. I am
inclined
to
store the actual value in such a case, reasoning that I am storing a
value
in a single field whether that field contains an ID or the actual
value,
but
you need to decide what is to happen if the value changes.

There are a number of approaches depending on the details, so I will
put
a
few questions forward and wait for your responses. Is the idea that
you
will select a Course record and then populate it with Employees, or
the
other way around? Is a Course a one-time thing, or is it repeated
from
time
to time? Where I work we have certain recurring training (First Aid,
etc.),
but rarely if ever is it the exact same training twice in a row, so I
decided to create each Session record as needed. Again, the details
of
your
situation will affect how you proceed.

"scubadiver" wrote in message
...
thanks for the reply...

Are supervisors employees? "Yes"
Does a non-supervisor ever become a supervisor? On the side of
caution,
"yes"
Do supervisors ever take courses? On the side of caution, "yes"
Yes/No field to designate them as supervisors: very good idea !!!

"an option group in tblCourse is one way of accomplishing what I
assume
is
the need to group courses according to type". Not necessarily a
need,
but
would be useful. There are LOTs of courses. Could use separate look
up
tables
using a cascading combo?

So if I have you correctly, I could have just the following:

tble_course
CourseID (PK)
CourseType
CourseName

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

I am wondering whether I need three tables? Why not just two:
employee
and
course.

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

tble_empcourse
EmployeeID (FK)
CourseType
CourseName
TrainingDate
Instructor

with two separate tables to act as course type and name look up
tables?

cheers!

"BruceM" wrote:

Are supervisors employees? Does a non-supervisor ever become a
supervisor?
Do supervisors ever take courses? I repeat the earlier suggestion
that
supervisors and employees should be in the same table, with a
Yes/No
field
to designate them as supervisors. This will let you query for all
records
in which that field is True, which presents the same information as
a
Supervisor table, but with a fraction of the maintenance and other
hassles
that will surely result.
Also, Name should not be a PK field, and it should not be the name
of
a
field since Name is a reserved word in Access. Perhaps it was just
a
shortcut you used when posting the table structure, but I thought
I'd
mention it anyhow.
Is SupID related to the PK field from tblSupervisor? I don't see a
SupID
field anywhere as a PK, so it's not entirely clear.
I'm not sure there's much point to having a CourseType table; an
option
group in tblCourse is one way of accomplishing what I assume is the
need
to
group courses according to type.
If the same course is offered twice, and is taught by a different
supervisor
each time, you will need to either add a new course record or
modify
the
existing one. If you do the latter you won't have a record of who
taught
the course at any particular time. If you do the latter you will
have
some
redundancy. You may want a table related one-to-many with the
course
table
to store course dates and instructors, so that you can see when the
course
was taught and who taught it.
I would give all tables a PK field, including the junction table,
which
does
not show a PK field in your listing. You may not need it now, but
it
gives
you flexibility going forward.
I urge you to reconsider using a separate Supervisor table if any
of
the
conditions I mentioned could be true. The other observations and
questions
are things you may want to consider, depending on the details of
your
situation. In general the structure looks pretty good.

"scubadiver" wrote in
message
...
Thanks for the reply.

I don't have a great deal of understanding with databases of this
complexity
and this is a good time to learn. I have changed slightly what
you
have
suggested. In the relationship table I have the following from
left
to
right:

tble_coursetype
TypeID (PK)
TypeName

tble_supervisor
Name (PK)
Initials

tble_course
courseID (PK)
coursename
TypeID (FK)
SupID (FK)

tble_empcourse
EmployeeID (FK)
CourseID (FK)
TrainingDate

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept



Is it correct that it is better to deal with two forms at a time
if
they
have a 1:n relationship? "tble_course" as above requires ID
numbers
from
two
other separate tables. Having three separate tables makes it very
difficult
to understand how to enter the information into forms.

I am wondering whether Karl does have a point, though it makes
sense
to
put
supervisor details in with "tble_course" rather than
"tble_employee".

Also, each job role will have its own list of training courses
which

At the moment I have a main form that contains employee information (name,
department etc) and a subform that would contain the training course
information (course type, course name etc)

Im not trying to be awkward but

(a) I am not sure what you mean by "storing attendance". All the people that
attend a certain course?

(b) "in what field would you add Johnny's information?" What information are
you referring to?

"BruceM" wrote:

There is no good way of storing attendance in the course record. If the
Course is Basic Safety Practices, in what field would you add Johnny's
information? How about Jill's, or the hundredth person to take the course?
The junction table is essential.

"scubadiver" wrote in message
...
The attendance would be be in "tble_course", not "tble_employee"

Going back to your first paragraph, I will apply your example.

I would go to Johnny's record and, in the subform, I would select the
Basic
Safety Practices Course in the subform so that course is attached to his
name. I would then go to Jill Brown's record and do the same.

"BruceM" wrote:

What fields are involved in the one-to-many relationship between
tblEmployee
and tblCourse? If Johnny Jones takes the Basic Safety Practices course,
you
store his ID in the Basic Safety Practices record in tblCourse? When
Jill
Brown takes the same course, where do you store her ID? When you are
setting up the course, how do you store the attendance, in tblCourse or
in
tblEmployee?
A principle of relational database design is that a table contains
information about a single entity such as Employee, Course, or Attendee.
Employee is not an attribute of a Course, nor is Course an attribute of
an
Employee.
The thing I don't get here is that you described a pretty sound design,
but
now you suddenly seem to be saying you don't want to do it that way.
Perhaps I don't understand the situation. Could you describe with some
pared-down examples what you mean when you say that a job title may have
many descriptions and vice versa. How do you populate a job description
with job titles? Do you have a certain number of fields for Job Titles
in a
single Description record?

"scubadiver" wrote in message
...
Each training will happen when required.

At the moment, "tble_employee" and "tble_course" has a 1:n
relationship. I
want to select an employee and populate it with the courses they
attend.

But to give a different example I have created another database for my
dad.
He wanted a db to hold job titles and job descriptions. I decided to
design
the structure so that I would populate each job description with job
titles
because there are hundreds of job descriptions and each has lots of
text
(it
would be too cumbersome)

I don't have a junction table even though each job title has many
descriptions and each description can be attached to many job titles.
Even
with this design I can still create a query whereby I produce a report
to
list all the descriptions for a particular job title.

I can't see why I can't do the same for the training database. I can
produce
a query that lists all the employees that have been on a given training
course.

cheers.

"BruceM" wrote:

Last question first: each employee may take many courses, and each
course
may be attended by many employees, so there is a many-to-many
relationship
between employees and courses; therefore, a junction table is needed
to
resolve the relationship. Your original tblEmplCourse was the right
idea.
Your most recent one contains fields that are already in the course
table:

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

I would give the table a PK, and eliminate all or most of the non-FK
fields:

tblEmpCourse
EmpCourseID (PK)
EmployeeID (FK)
CourseID (FK)

You may want a comments field. If the course is on a particular date,
the
date field may not be necessary. Where I work the training may take
place
on different days for different employees (different shifts, etc.),
but
there are training sessions as needed (for instance, a new process)
rather
than preset courses. You just need to make the best choice for your
situation. In any case, CourseType, CourseName, and Instructor are
part
of
the Course record, so storing them in the junction table is probably
redundant. They are already part of the record identified by the
CourseID.

I'm not so much saying the CourseType table is not needed as I am
asking
whether it is. If you store the CourseType in tblCourse or you have a
separate tblCourseType you can filter the Course records to see a
listing
by
type or to provide the row source for a combo box. With a linked
CourseType
table a change to the name of a CourseType will be reflected in all
records
in which the CourseID was stored; if you store the name of the course
that
name will be preserved even if the CourseType is renamed. I am
inclined
to
store the actual value in such a case, reasoning that I am storing a
value
in a single field whether that field contains an ID or the actual
value,
but
you need to decide what is to happen if the value changes.

There are a number of approaches depending on the details, so I will
put
a
few questions forward and wait for your responses. Is the idea that
you
will select a Course record and then populate it with Employees, or
the
other way around? Is a Course a one-time thing, or is it repeated
from
time
to time? Where I work we have certain recurring training (First Aid,
etc.),
but rarely if ever is it the exact same training twice in a row, so I
decided to create each Session record as needed. Again, the details
of
your
situation will affect how you proceed.

"scubadiver" wrote in message
...
thanks for the reply...

Are supervisors employees? "Yes"
Does a non-supervisor ever become a supervisor? On the side of
caution,
"yes"
Do supervisors ever take courses? On the side of caution, "yes"
Yes/No field to designate them as supervisors: very good idea !!!

"an option group in tblCourse is one way of accomplishing what I
assume
is
the need to group courses according to type". Not necessarily a
need,
but
would be useful. There are LOTs of courses. Could use separate look
up
tables
using a cascading combo?

So if I have you correctly, I could have just the following:

tble_course
CourseID (PK)
CourseType
CourseName

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

I am wondering whether I need three tables? Why not just two:
employee
and
course.

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

tble_empcourse
EmployeeID (FK)
CourseType
CourseName
TrainingDate
Instructor

with two separate tables to act as course type and name look up
tables?

cheers!

"BruceM" wrote:

Are supervisors employees? Does a non-supervisor ever become a
supervisor?
Do supervisors ever take courses? I repeat the earlier suggestion
that
supervisors and employees should be in the same table, with a
Yes/No
field
to designate them as supervisors. This will let you query for all
records
in which that field is True, which presents the same information as
a
Supervisor table, but with a fraction of the maintenance and other
hassles
that will surely result.
Also, Name should not be a PK field, and it should not be the name
of
a
field since Name is a reserved word in Access. Perhaps it was just
a
shortcut you used when posting the table structure, but I thought
I'd
mention it anyhow.
Is SupID related to the PK field from tblSupervisor? I don't see a
SupID
field anywhere as a PK, so it's not entirely clear.
I'm not sure there's much point to having a CourseType table; an
option
group in tblCourse is one way of accomplishing what I assume is the
need
to
group courses according to type.
If the same course is offered twice, and is taught by a different
supervisor
each time, you will need to either add a new course record or
modify
the
existing one. If you do the latter you won't have a record of who
taught
the course at any particular time. If you do the latter you will
have
some
redundancy. You may want a table related one-to-many with the
course
table
to store course dates and instructors, so that you can see when the
course
was taught and who taught it.
I would give all tables a PK field, including the junction table,
which
does
not show a PK field in your listing. You may not need it now, but
it
gives
you flexibility going forward.
I urge you to reconsider using a separate Supervisor table if any
of
the
conditions I mentioned could be true. The other observations and
questions
are things you may want to consider, depending on the details of
your
situation. In general the structure looks pretty good.

I found a m:m relationship db in Rogers Access Library. Maybe it will help!!

"BruceM" wrote:

Let's say that you want to populate the list of Responsibilities
(Descriptions) with job titles to which the Responsibility applies. In that
case you would use a JobTitle table, a Descriptions table, and a junction
table (let's call it tblDuties) between the two. There is a one-to-many
relationship between both tblResponsibilities and tblJobTitle and tblDuties:

tblResponsibilities
ResponsibilityID (PK)
RespDescription

tblJobTitle
JobTitleID (PK)
JobDescription

tblDuties (junction table)
DutyID (PK)
ResponsibilityID (FK)
JobTitleID (FK)

You could have other fields as well, depending on your needs. JobTitle may
include a ReportsTo field, or Department, or whatever.

In this scenario there is a one-to-many relationship between the PK fields
and the FK fields of the same name. That they have the same name here is
for convenience; it is not required. Remember that PK is defined in table
design view, but FK is defined only by its relationship to a PK field.

Once the relationships are defined it is time for the forms. Create a form
based on tblResponsibilites (frmResp) and another (fsubDuties) based on
tblDuties. Set the Default View on the property sheet for fsubDuties to
Continuous. Bind a combo box on fsubDuties to JobTitleID (it will be the
JobTitleID foreign key field from tblDuties). In the combo box property
sheet, click the Data tab, click Row Source, and click the three dots.
Select tblJobTitle, and add both fields (it would probably be a good idea to
sort by JobDescription while you're at it). Close the query, and update
when prompted. Still on the Data tab, set the bound column to 1 (assuming
JobTitleID is in the first column). Click the Format tab, and set the
Column Count to 2 and the column widths to 0";1" (or whatever you need for
the second column).
Save and close fsubJobTitle. Open frmResp in design view. Press F11 to
view the database window. Click the Forms tab, and drag the icon for
fsubJobTitle onto frmResp. Click View Properties (these are the
properties for the subform control, as the form you added is now called),
click the Data tab, and verify that ResponsibilityID is in the Link Parent
Field and Link Child Field rows. If it is not, click into one of the rows,
click the three dots, and follow the prompts and suggestions.
Switch to Form view. Your main form record is a Responsibility record, and
your subform may be used to list jobs to which that Responsibility applies.
In similar fashion you can build a main form based on tblJobTitle and a
subform based on tblDuties (the subform is always based on the junction
table), except that the combo box is now based on tblResponsibilites, and
the Link fields are JobTitleID. The same idea applies to a TrainingSession
main form and an Attendance subform, with the combo box on the subform
getting its Row Source from tblEmployee.
You can make a report / subreport in the same general way, except that the
subreport will be based on a query combining the second table (the one used
for the combo box row source) and the junction table. You can use the
report's Sorting and Grouping to arrange the data as you choose.
In general, one of the tables in a relationship including a junction table
is a relatively static table. In the first example (with the JobDescription
main form) the JobTitles are what I am calling "static", or less likely to
need updating. If you do need to update or add a JobTitle, you will need a
separate form based on tblJobTitle for that task. Once a JobTitle is added
to tblJobTitle it will be available as a selection from the combo box.
Records can be added as you go using the main form, but not so with the
table being used for the combo box Row Source.
There are all kinds of variations, but this is the basic idea of a setup
when there is a many-to-many (junction table) structure. I had a difficult
time at first grasping many-to-many relationships, but once I got them my
thinking about database design underwent a very, very important
transformation.

"scubadiver" wrote in message
...
I seem to think that using a junction table confuses me so maybe I am
reluctant to use it because I am not sure how to use it for data entry
purposes.

How do you populate a job description with job titles? Do you have a
certain number of fields for Job Titles in a single Description record?
When
I say "job description" maybe I should say "task descriptions" (when you
apply for a job you get a document with a list of tasks that go with the
job).

The main form is for the job description and the subform is for the job
titles so I have as many titles as is required for that job description
(1:n
relationship). For example, the job description may be, for simplicity,
"Write a report on a monthly basis", and I can attach all the relevant
titles
that requires that description.

If I have 300 job descriptions and 20 job titles and each job description
has a number of titles attached as appropriate I can create a query that
puts
all the fields together. I can then filter the records for a certain job
title and I will get a list of all the appropriate job descriptions. This
works fine even though the structure is the other way round.

In principle I can understand why a junction box should be used. In
practice, it makes form design and data entry difficult to understand, for
me
anyway.

"BruceM" wrote:

What fields are involved in the one-to-many relationship between
tblEmployee
and tblCourse? If Johnny Jones takes the Basic Safety Practices course,
you
store his ID in the Basic Safety Practices record in tblCourse? When
Jill
Brown takes the same course, where do you store her ID? When you are
setting up the course, how do you store the attendance, in tblCourse or
in
tblEmployee?
A principle of relational database design is that a table contains
information about a single entity such as Employee, Course, or Attendee.
Employee is not an attribute of a Course, nor is Course an attribute of
an
Employee.
The thing I don't get here is that you described a pretty sound design,
but
now you suddenly seem to be saying you don't want to do it that way.
Perhaps I don't understand the situation. Could you describe with some
pared-down examples what you mean when you say that a job title may have
many descriptions and vice versa. How do you populate a job description
with job titles? Do you have a certain number of fields for Job Titles
in a
single Description record?

"scubadiver" wrote in message
...
Each training will happen when required.

At the moment, "tble_employee" and "tble_course" has a 1:n
relationship. I
want to select an employee and populate it with the courses they
attend.

But to give a different example I have created another database for my
dad.
He wanted a db to hold job titles and job descriptions. I decided to
design
the structure so that I would populate each job description with job
titles
because there are hundreds of job descriptions and each has lots of
text
(it
would be too cumbersome)

I don't have a junction table even though each job title has many
descriptions and each description can be attached to many job titles.
Even
with this design I can still create a query whereby I produce a report
to
list all the descriptions for a particular job title.

I can't see why I can't do the same for the training database. I can
produce
a query that lists all the employees that have been on a given training
course.

cheers.

"BruceM" wrote:

Last question first: each employee may take many courses, and each
course
may be attended by many employees, so there is a many-to-many
relationship
between employees and courses; therefore, a junction table is needed
to
resolve the relationship. Your original tblEmplCourse was the right
idea.
Your most recent one contains fields that are already in the course
table:

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

I would give the table a PK, and eliminate all or most of the non-FK
fields:

tblEmpCourse
EmpCourseID (PK)
EmployeeID (FK)
CourseID (FK)

You may want a comments field. If the course is on a particular date,
the
date field may not be necessary. Where I work the training may take
place
on different days for different employees (different shifts, etc.),
but
there are training sessions as needed (for instance, a new process)
rather
than preset courses. You just need to make the best choice for your
situation. In any case, CourseType, CourseName, and Instructor are
part
of
the Course record, so storing them in the junction table is probably
redundant. They are already part of the record identified by the
CourseID.

I'm not so much saying the CourseType table is not needed as I am
asking
whether it is. If you store the CourseType in tblCourse or you have a
separate tblCourseType you can filter the Course records to see a
listing
by
type or to provide the row source for a combo box. With a linked
CourseType
table a change to the name of a CourseType will be reflected in all
records
in which the CourseID was stored; if you store the name of the course
that
name will be preserved even if the CourseType is renamed. I am
inclined
to
store the actual value in such a case, reasoning that I am storing a
value
in a single field whether that field contains an ID or the actual
value,
but
you need to decide what is to happen if the value changes.

There are a number of approaches depending on the details, so I will
put
a
few questions forward and wait for your responses. Is the idea that
you
will select a Course record and then populate it with Employees, or
the
other way around? Is a Course a one-time thing, or is it repeated
from
time
to time? Where I work we have certain recurring training (First Aid,
etc.),
but rarely if ever is it the exact same training twice in a row, so I
decided to create each Session record as needed. Again, the details
of
your
situation will affect how you proceed.

"scubadiver" wrote in message
...
thanks for the reply...

Are supervisors employees? "Yes"
Does a non-supervisor ever become a supervisor? On the side of
caution,
"yes"
Do supervisors ever take courses? On the side of caution, "yes"
Yes/No field to designate them as supervisors: very good idea !!!

"an option group in tblCourse is one way of accomplishing what I
assume
is
the need to group courses according to type". Not necessarily a
need,
but
would be useful. There are LOTs of courses. Could use separate look
up
tables
using a cascading combo?

So if I have you correctly, I could have just the following:

tble_course
CourseID (PK)
CourseType
CourseName

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

I am wondering whether I need three tables? Why not just two:
employee

If I have a 1:m relationship between name and course and create a query I can
get the following example:

Name Course

John A
John B
John C
John D
Sally A
Sally B
Sally D
Bill B
Bill C
Edgar A
Edgar C
Edgar D
Bryan B
Bryan D

I can filter the query to get names for a specific course:

Who attended course A?

John
Sally
Edgar

Who attended course B?

John
Sally
Bill
Bryan

Who attended Course C?

John
Bill
Edgar

Who attended course D?

John
Sally
Edgar
Bryan


"BruceM" wrote:

There is no good way of storing attendance in the course record. If the
Course is Basic Safety Practices, in what field would you add Johnny's
information? How about Jill's, or the hundredth person to take the course?
The junction table is essential.

"scubadiver" wrote in message
...
The attendance would be be in "tble_course", not "tble_employee"

Going back to your first paragraph, I will apply your example.

I would go to Johnny's record and, in the subform, I would select the
Basic
Safety Practices Course in the subform so that course is attached to his
name. I would then go to Jill Brown's record and do the same.

"BruceM" wrote:

What fields are involved in the one-to-many relationship between
tblEmployee
and tblCourse? If Johnny Jones takes the Basic Safety Practices course,
you
store his ID in the Basic Safety Practices record in tblCourse? When
Jill
Brown takes the same course, where do you store her ID? When you are
setting up the course, how do you store the attendance, in tblCourse or
in
tblEmployee?
A principle of relational database design is that a table contains
information about a single entity such as Employee, Course, or Attendee.
Employee is not an attribute of a Course, nor is Course an attribute of
an
Employee.
The thing I don't get here is that you described a pretty sound design,
but
now you suddenly seem to be saying you don't want to do it that way.
Perhaps I don't understand the situation. Could you describe with some
pared-down examples what you mean when you say that a job title may have
many descriptions and vice versa. How do you populate a job description
with job titles? Do you have a certain number of fields for Job Titles
in a
single Description record?

"scubadiver" wrote in message
...
Each training will happen when required.

At the moment, "tble_employee" and "tble_course" has a 1:n
relationship. I
want to select an employee and populate it with the courses they
attend.

But to give a different example I have created another database for my
dad.
He wanted a db to hold job titles and job descriptions. I decided to
design
the structure so that I would populate each job description with job
titles
because there are hundreds of job descriptions and each has lots of
text
(it
would be too cumbersome)

I don't have a junction table even though each job title has many
descriptions and each description can be attached to many job titles.
Even
with this design I can still create a query whereby I produce a report
to
list all the descriptions for a particular job title.

I can't see why I can't do the same for the training database. I can
produce
a query that lists all the employees that have been on a given training
course.

cheers.

"BruceM" wrote:

Last question first: each employee may take many courses, and each
course
may be attended by many employees, so there is a many-to-many
relationship
between employees and courses; therefore, a junction table is needed
to
resolve the relationship. Your original tblEmplCourse was the right
idea.
Your most recent one contains fields that are already in the course
table:

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

I would give the table a PK, and eliminate all or most of the non-FK
fields:

tblEmpCourse
EmpCourseID (PK)
EmployeeID (FK)
CourseID (FK)

You may want a comments field. If the course is on a particular date,
the
date field may not be necessary. Where I work the training may take
place
on different days for different employees (different shifts, etc.),
but
there are training sessions as needed (for instance, a new process)
rather
than preset courses. You just need to make the best choice for your
situation. In any case, CourseType, CourseName, and Instructor are
part
of
the Course record, so storing them in the junction table is probably
redundant. They are already part of the record identified by the
CourseID.

I'm not so much saying the CourseType table is not needed as I am
asking
whether it is. If you store the CourseType in tblCourse or you have a
separate tblCourseType you can filter the Course records to see a
listing
by
type or to provide the row source for a combo box. With a linked
CourseType
table a change to the name of a CourseType will be reflected in all
records
in which the CourseID was stored; if you store the name of the course
that
name will be preserved even if the CourseType is renamed. I am
inclined
to
store the actual value in such a case, reasoning that I am storing a
value
in a single field whether that field contains an ID or the actual
value,
but
you need to decide what is to happen if the value changes.

There are a number of approaches depending on the details, so I will
put
a
few questions forward and wait for your responses. Is the idea that
you
will select a Course record and then populate it with Employees, or
the
other way around? Is a Course a one-time thing, or is it repeated
from
time
to time? Where I work we have certain recurring training (First Aid,
etc.),
but rarely if ever is it the exact same training twice in a row, so I
decided to create each Session record as needed. Again, the details
of
your
situation will affect how you proceed.

"scubadiver" wrote in message
...
thanks for the reply...

Are supervisors employees? "Yes"
Does a non-supervisor ever become a supervisor? On the side of
caution,
"yes"
Do supervisors ever take courses? On the side of caution, "yes"
Yes/No field to designate them as supervisors: very good idea !!!

"an option group in tblCourse is one way of accomplishing what I
assume
is
the need to group courses according to type". Not necessarily a
need,
but
would be useful. There are LOTs of courses. Could use separate look
up
tables
using a cascading combo?

So if I have you correctly, I could have just the following:

tble_course
CourseID (PK)
CourseType
CourseName

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

I am wondering whether I need three tables? Why not just two:
employee
and
course.

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

tble_empcourse
EmployeeID (FK)
CourseType
CourseName
TrainingDate
Instructor

with two separate tables to act as course type and name look up
tables?

cheers!

"BruceM" wrote:

Are supervisors employees? Does a non-supervisor ever become a
supervisor?
Do supervisors ever take courses? I repeat the earlier suggestion
that
supervisors and employees should be in the same table, with a
Yes/No
field
to designate them as supervisors. This will let you query for all
records
in which that field is True, which presents the same information as
a
Supervisor table, but with a fraction of the maintenance and other
hassles
that will surely result.
Also, Name should not be a PK field, and it should not be the name
of
a
field since Name is a reserved word in Access. Perhaps it was just
a
shortcut you used when posting the table structure, but I thought
I'd
mention it anyhow.
Is SupID related to the PK field from tblSupervisor? I don't see a
SupID
field anywhere as a PK, so it's not entirely clear.
I'm not sure there's much point to having a CourseType table; an
option
group in tblCourse is one way of accomplishing what I assume is the
need
to
group courses according to type.
If the same course is offered twice, and is taught by a different
supervisor
each time, you will need to either add a new course record or
modify
the
existing one. If you do the latter you won't have a record of who
taught
the course at any particular time. If you do the latter you will
have
some
redundancy. You may want a table related one-to-many with the
course
table
to store course dates and instructors, so that you can see when the
course
was taught and who taught it.
I would give all tables a PK field, including the junction table,
which
does
not show a PK field in your listing. You may not need it now, but
it
gives
you flexibility going forward.
I urge you to reconsider using a separate Supervisor table if any
of
the
conditions I mentioned could be true. The other observations and
questions
are things you may want to consider, depending on the details of
your
situation. In general the structure looks pretty good.

Sorry to hear my explanation was of so little value that your only reply was
to tell me you would look elsewhere.

"scubadiver" wrote in message
...

I found a m:m relationship db in Rogers Access Library. Maybe it will
help!!

"BruceM" wrote:

Let's say that you want to populate the list of Responsibilities
(Descriptions) with job titles to which the Responsibility applies. In
that
case you would use a JobTitle table, a Descriptions table, and a junction
table (let's call it tblDuties) between the two. There is a one-to-many
relationship between both tblResponsibilities and tblJobTitle and
tblDuties:

tblResponsibilities
ResponsibilityID (PK)
RespDescription

tblJobTitle
JobTitleID (PK)
JobDescription

tblDuties (junction table)
DutyID (PK)
ResponsibilityID (FK)
JobTitleID (FK)

You could have other fields as well, depending on your needs. JobTitle
may
include a ReportsTo field, or Department, or whatever.

In this scenario there is a one-to-many relationship between the PK
fields
and the FK fields of the same name. That they have the same name here is
for convenience; it is not required. Remember that PK is defined in
table
design view, but FK is defined only by its relationship to a PK field.

Once the relationships are defined it is time for the forms. Create a
form
based on tblResponsibilites (frmResp) and another (fsubDuties) based on
tblDuties. Set the Default View on the property sheet for fsubDuties to
Continuous. Bind a combo box on fsubDuties to JobTitleID (it will be the
JobTitleID foreign key field from tblDuties). In the combo box property
sheet, click the Data tab, click Row Source, and click the three dots.
Select tblJobTitle, and add both fields (it would probably be a good idea
to
sort by JobDescription while you're at it). Close the query, and update
when prompted. Still on the Data tab, set the bound column to 1
(assuming
JobTitleID is in the first column). Click the Format tab, and set the
Column Count to 2 and the column widths to 0";1" (or whatever you need
for
the second column).
Save and close fsubJobTitle. Open frmResp in design view. Press F11 to
view the database window. Click the Forms tab, and drag the icon for
fsubJobTitle onto frmResp. Click View Properties (these are the
properties for the subform control, as the form you added is now called),
click the Data tab, and verify that ResponsibilityID is in the Link
Parent
Field and Link Child Field rows. If it is not, click into one of the
rows,
click the three dots, and follow the prompts and suggestions.
Switch to Form view. Your main form record is a Responsibility record,
and
your subform may be used to list jobs to which that Responsibility
applies.
In similar fashion you can build a main form based on tblJobTitle and a
subform based on tblDuties (the subform is always based on the junction
table), except that the combo box is now based on tblResponsibilites, and
the Link fields are JobTitleID. The same idea applies to a
TrainingSession
main form and an Attendance subform, with the combo box on the subform
getting its Row Source from tblEmployee.
You can make a report / subreport in the same general way, except that
the
subreport will be based on a query combining the second table (the one
used
for the combo box row source) and the junction table. You can use the
report's Sorting and Grouping to arrange the data as you choose.
In general, one of the tables in a relationship including a junction
table
is a relatively static table. In the first example (with the
JobDescription
main form) the JobTitles are what I am calling "static", or less likely
to
need updating. If you do need to update or add a JobTitle, you will need
a
separate form based on tblJobTitle for that task. Once a JobTitle is
added
to tblJobTitle it will be available as a selection from the combo box.
Records can be added as you go using the main form, but not so with the
table being used for the combo box Row Source.
There are all kinds of variations, but this is the basic idea of a setup
when there is a many-to-many (junction table) structure. I had a
difficult
time at first grasping many-to-many relationships, but once I got them my
thinking about database design underwent a very, very important
transformation.

"scubadiver" wrote in message
...
I seem to think that using a junction table confuses me so maybe I am
reluctant to use it because I am not sure how to use it for data entry
purposes.

How do you populate a job description with job titles? Do you have a
certain number of fields for Job Titles in a single Description record?
When
I say "job description" maybe I should say "task descriptions" (when
you
apply for a job you get a document with a list of tasks that go with
the
job).

The main form is for the job description and the subform is for the job
titles so I have as many titles as is required for that job description
(1:n
relationship). For example, the job description may be, for simplicity,
"Write a report on a monthly basis", and I can attach all the relevant
titles
that requires that description.

If I have 300 job descriptions and 20 job titles and each job
description
has a number of titles attached as appropriate I can create a query
that
puts
all the fields together. I can then filter the records for a certain
job
title and I will get a list of all the appropriate job descriptions.
This
works fine even though the structure is the other way round.

In principle I can understand why a junction box should be used. In
practice, it makes form design and data entry difficult to understand,
for
me
anyway.

"BruceM" wrote:

What fields are involved in the one-to-many relationship between
tblEmployee
and tblCourse? If Johnny Jones takes the Basic Safety Practices
course,
you
store his ID in the Basic Safety Practices record in tblCourse? When
Jill
Brown takes the same course, where do you store her ID? When you are
setting up the course, how do you store the attendance, in tblCourse
or
in
tblEmployee?
A principle of relational database design is that a table contains
information about a single entity such as Employee, Course, or
Attendee.
Employee is not an attribute of a Course, nor is Course an attribute
of
an
Employee.
The thing I don't get here is that you described a pretty sound
design,
but
now you suddenly seem to be saying you don't want to do it that way.
Perhaps I don't understand the situation. Could you describe with
some
pared-down examples what you mean when you say that a job title may
have
many descriptions and vice versa. How do you populate a job
description
with job titles? Do you have a certain number of fields for Job
Titles
in a
single Description record?

"scubadiver" wrote in message
...
Each training will happen when required.

At the moment, "tble_employee" and "tble_course" has a 1:n
relationship. I
want to select an employee and populate it with the courses they
attend.

But to give a different example I have created another database for
my
dad.
He wanted a db to hold job titles and job descriptions. I decided to
design
the structure so that I would populate each job description with job
titles
because there are hundreds of job descriptions and each has lots of
text
(it
would be too cumbersome)

I don't have a junction table even though each job title has many
descriptions and each description can be attached to many job
titles.
Even
with this design I can still create a query whereby I produce a
report
to
list all the descriptions for a particular job title.

I can't see why I can't do the same for the training database. I can
produce
a query that lists all the employees that have been on a given
training
course.

cheers.

"BruceM" wrote:

Last question first: each employee may take many courses, and each
course
may be attended by many employees, so there is a many-to-many
relationship
between employees and courses; therefore, a junction table is
needed
to
resolve the relationship. Your original tblEmplCourse was the
right
idea.
Your most recent one contains fields that are already in the course
table:

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

I would give the table a PK, and eliminate all or most of the
non-FK
fields:

tblEmpCourse
EmpCourseID (PK)
EmployeeID (FK)
CourseID (FK)

You may want a comments field. If the course is on a particular
date,
the
date field may not be necessary. Where I work the training may
take
place
on different days for different employees (different shifts, etc.),
but
there are training sessions as needed (for instance, a new process)
rather
than preset courses. You just need to make the best choice for
your
situation. In any case, CourseType, CourseName, and Instructor are
part
of
the Course record, so storing them in the junction table is
probably
redundant. They are already part of the record identified by the
CourseID.

I'm not so much saying the CourseType table is not needed as I am
asking
whether it is. If you store the CourseType in tblCourse or you
have a
separate tblCourseType you can filter the Course records to see a
listing
by
type or to provide the row source for a combo box. With a linked
CourseType
table a change to the name of a CourseType will be reflected in all
records
in which the CourseID was stored; if you store the name of the
course
that
name will be preserved even if the CourseType is renamed. I am
inclined
to
store the actual value in such a case, reasoning that I am storing
a
value
in a single field whether that field contains an ID or the actual
value,
but
you need to decide what is to happen if the value changes.

There are a number of approaches depending on the details, so I
will
put
a
few questions forward and wait for your responses. Is the idea
that
you
will select a Course record and then populate it with Employees, or
the
other way around? Is a Course a one-time thing, or is it repeated
from
time
to time? Where I work we have certain recurring training (First
Aid,
etc.),
but rarely if ever is it the exact same training twice in a row, so
I
decided to create each Session record as needed. Again, the
details
of
your
situation will affect how you proceed.

"scubadiver" wrote in
message
...
thanks for the reply...

Are supervisors employees? "Yes"
Does a non-supervisor ever become a supervisor? On the side of
caution,
"yes"
Do supervisors ever take courses? On the side of caution, "yes"
Yes/No field to designate them as supervisors: very good idea !!!

"an option group in tblCourse is one way of accomplishing what I
assume
is
the need to group courses according to type". Not necessarily a
need,
but
would be useful. There are LOTs of courses. Could use separate
look
up
tables
using a cascading combo?

So if I have you correctly, I could have just the following:

tble_course
CourseID (PK)
CourseType
CourseName

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

I am wondering whether I need three tables? Why not just two:
employee

See inline.

"scubadiver" wrote in message
...
At the moment I have a main form that contains employee information (name,
department etc) and a subform that would contain the training course
information (course type, course name etc)

Im not trying to be awkward but

(a) I am not sure what you mean by "storing attendance". All the people
that
attend a certain course?

Yes. If the course is Basic Safety there is a record in the Course table
that contains information about that course, but not about the people who
attended it. That information is a separate entity from the course
description, so it needs its own table. If you set up a sample database
with just a few records according to the instructions I gave at some length
in another post in this thread it may become clearer how the pieces come
together.

tblCourse

CourseID CourseDescription
0001 Basic Safety
0002 Order Tracking Program

and so forth. There could be other fields pertaining to the course, but
there is no place in the Course record for a list of people who have
attended the course.


(b) "in what field would you add Johnny's information?" What information
are
you referring to?

I meant Johnny's ID, which is stored in the junction table along with the
Course ID.

"BruceM" wrote:

There is no good way of storing attendance in the course record. If the
Course is Basic Safety Practices, in what field would you add Johnny's
information? How about Jill's, or the hundredth person to take the
course?
The junction table is essential.

"scubadiver" wrote in message
...
The attendance would be be in "tble_course", not "tble_employee"

Going back to your first paragraph, I will apply your example.

I would go to Johnny's record and, in the subform, I would select the
Basic
Safety Practices Course in the subform so that course is attached to
his
name. I would then go to Jill Brown's record and do the same.

"BruceM" wrote:

What fields are involved in the one-to-many relationship between
tblEmployee
and tblCourse? If Johnny Jones takes the Basic Safety Practices
course,
you
store his ID in the Basic Safety Practices record in tblCourse? When
Jill
Brown takes the same course, where do you store her ID? When you are
setting up the course, how do you store the attendance, in tblCourse
or
in
tblEmployee?
A principle of relational database design is that a table contains
information about a single entity such as Employee, Course, or
Attendee.
Employee is not an attribute of a Course, nor is Course an attribute
of
an
Employee.
The thing I don't get here is that you described a pretty sound
design,
but
now you suddenly seem to be saying you don't want to do it that way.
Perhaps I don't understand the situation. Could you describe with
some
pared-down examples what you mean when you say that a job title may
have
many descriptions and vice versa. How do you populate a job
description
with job titles? Do you have a certain number of fields for Job
Titles
in a
single Description record?

"scubadiver" wrote in message
...
Each training will happen when required.

At the moment, "tble_employee" and "tble_course" has a 1:n
relationship. I
want to select an employee and populate it with the courses they
attend.

But to give a different example I have created another database for
my
dad.
He wanted a db to hold job titles and job descriptions. I decided to
design
the structure so that I would populate each job description with job
titles
because there are hundreds of job descriptions and each has lots of
text
(it
would be too cumbersome)

I don't have a junction table even though each job title has many
descriptions and each description can be attached to many job
titles.
Even
with this design I can still create a query whereby I produce a
report
to
list all the descriptions for a particular job title.

I can't see why I can't do the same for the training database. I can
produce
a query that lists all the employees that have been on a given
training
course.

cheers.

"BruceM" wrote:

Last question first: each employee may take many courses, and each
course
may be attended by many employees, so there is a many-to-many
relationship
between employees and courses; therefore, a junction table is
needed
to
resolve the relationship. Your original tblEmplCourse was the
right
idea.
Your most recent one contains fields that are already in the course
table:

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

I would give the table a PK, and eliminate all or most of the
non-FK
fields:

tblEmpCourse
EmpCourseID (PK)
EmployeeID (FK)
CourseID (FK)

You may want a comments field. If the course is on a particular
date,
the
date field may not be necessary. Where I work the training may
take
place
on different days for different employees (different shifts, etc.),
but
there are training sessions as needed (for instance, a new process)
rather
than preset courses. You just need to make the best choice for
your
situation. In any case, CourseType, CourseName, and Instructor are
part
of
the Course record, so storing them in the junction table is
probably
redundant. They are already part of the record identified by the
CourseID.

I'm not so much saying the CourseType table is not needed as I am
asking
whether it is. If you store the CourseType in tblCourse or you
have a
separate tblCourseType you can filter the Course records to see a
listing
by
type or to provide the row source for a combo box. With a linked
CourseType
table a change to the name of a CourseType will be reflected in all
records
in which the CourseID was stored; if you store the name of the
course
that
name will be preserved even if the CourseType is renamed. I am
inclined
to
store the actual value in such a case, reasoning that I am storing
a
value
in a single field whether that field contains an ID or the actual
value,
but
you need to decide what is to happen if the value changes.

There are a number of approaches depending on the details, so I
will
put
a
few questions forward and wait for your responses. Is the idea
that
you
will select a Course record and then populate it with Employees, or
the
other way around? Is a Course a one-time thing, or is it repeated
from
time
to time? Where I work we have certain recurring training (First
Aid,
etc.),
but rarely if ever is it the exact same training twice in a row, so
I
decided to create each Session record as needed. Again, the
details
of
your
situation will affect how you proceed.

"scubadiver" wrote in
message
...
thanks for the reply...

Are supervisors employees? "Yes"
Does a non-supervisor ever become a supervisor? On the side of
caution,
"yes"
Do supervisors ever take courses? On the side of caution, "yes"
Yes/No field to designate them as supervisors: very good idea !!!

"an option group in tblCourse is one way of accomplishing what I
assume
is
the need to group courses according to type". Not necessarily a
need,
but
would be useful. There are LOTs of courses. Could use separate
look
up
tables
using a cascading combo?

So if I have you correctly, I could have just the following:

tble_course
CourseID (PK)
CourseType
CourseName

tble_empcourse
EmployeeID (FK)
CourseID (FK)
CourseType
CourseName
TrainingDate
Instructor

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

I am wondering whether I need three tables? Why not just two:
employee
and
course.

tble_employee
EmployeeID (PK)
Fname
Sname
Workstatus
Operations
Dept
Subdept
Supervisor

tble_empcourse
EmployeeID (FK)
CourseType
CourseName
TrainingDate
Instructor

with two separate tables to act as course type and name look up
tables?

cheers!

"BruceM" wrote:

Are supervisors employees? Does a non-supervisor ever become a
supervisor?
Do supervisors ever take courses? I repeat the earlier
suggestion
that
supervisors and employees should be in the same table, with a
Yes/No
field
to designate them as supervisors. This will let you query for
all
records
in which that field is True, which presents the same information
as
a
Supervisor table, but with a fraction of the maintenance and
other
hassles
that will surely result.
Also, Name should not be a PK field, and it should not be the
name
of
a
field since Name is a reserved word in Access. Perhaps it was
just
a
shortcut you used when posting the table structure, but I
thought
I'd
mention it anyhow.
Is SupID related to the PK field from tblSupervisor? I don't
see a
SupID
field anywhere as a PK, so it's not entirely clear.
I'm not sure there's much point to having a CourseType table; an
option
group in tblCourse is one way of accomplishing what I assume is
the
need
to
group courses according to type.
If the same course is offered twice, and is taught by a
different
supervisor
each time, you will need to either add a new course record or
modify
the
existing one. If you do the latter you won't have a record of
who
taught
the course at any particular time. If you do the latter you
will
have
some
redundancy. You may want a table related one-to-many with the
course
table
to store course dates and instructors, so that you can see when
the
course
was taught and who taught it.
I would give all tables a PK field, including the junction
table,
which
does
not show a PK field in your listing. You may not need it now,
but
it
gives
you flexibility going forward.
I urge you to reconsider using a separate Supervisor table if
any
of
the
conditions I mentioned could be true. The other observations
and
questions
are things you may want to consider, depending on the details of
your
situation. In general the structure looks pretty good.