Training records

Is there a table with a listing of courses? How do the courses become parts
of the records you have described?

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

I have look up tables. The fields in tble_course will have combo boxes which
will select the appropriate course name depending on the course type, dept
and subdept.

The user hasn't got as far knowing which employees will have which courses
(but may likely depend on which subdept the employee is working in).

I appreciate the help about junction tables but I'm not convinced yet. In
principle it maybe considered important but for the practicalities of
entering information into a form, I still regard it as redundant. From my
example below, I don't need to establish a relationship that one course has
many employees when I can find this information just by using a query! Maybe
I'm missing something.

"BruceM" wrote:

Is there a table with a listing of courses? How do the courses become parts
of the records you have described?

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

Then go ahead and do whatever you want to do, but had I known your mind was
made up I would never have wasted my time attempting to explain junction
tables. But understand this: junction tables are not an abstract
theoretical concept, and they are not my personal little crusade; they are
*essential* to database design.

"scubadiver" wrote in message
...
I have look up tables. The fields in tble_course will have combo boxes
which
will select the appropriate course name depending on the course type, dept
and subdept.

The user hasn't got as far knowing which employees will have which courses
(but may likely depend on which subdept the employee is working in).

I appreciate the help about junction tables but I'm not convinced yet. In
principle it maybe considered important but for the practicalities of
entering information into a form, I still regard it as redundant. From my
example below, I don't need to establish a relationship that one course
has
many employees when I can find this information just by using a query!
Maybe
I'm missing something.

"BruceM" wrote:

Is there a table with a listing of courses? How do the courses become
parts
of the records you have described?

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

"BruceM" wrote:

but had I known your mind was made up I would never have wasted my time attempting to explain junction tables.

BruceM - and all the others who spend their valuable time answering
questions here -

Your time is NOT wasted. There are people like me that spend time just
reading through these discussion groups, picking up ideas, learning new
things.

Thanks for saying that. I too have picked up a great many things just be
reading messages. Some of the things I learned came from posts in which the
user resisted the suggestions. I usually don't let it get to me, but the
length of the exchange and the detail that went into it left me a bit
peevish, I guess.

"LauriS" wrote in message
...
"BruceM" wrote:

but had I known your mind was made up I would never have wasted my time
attempting to explain junction tables.

BruceM - and all the others who spend their valuable time answering
questions here -

Your time is NOT wasted. There are people like me that spend time just
reading through these discussion groups, picking up ideas, learning new
things.

One issue to consider in adding the supervisor field to the employee table:
IS it possible that an employee might, over the course of employment, have a
differnt supervisor. Might it be necessary at some point to report data based
on an employee's supervisor at the time a course was taken. Setting the
supervisor in teh employee table would almost certainly limit you to showing
data based on CURRENT supervisor only. I.e. if am empoyee is assigned to a
new supervisor then all courses taken by that empoyee are now linked to the
current supervisor.

I have a similiar situation, and used a "participant TRansaction table" much
like a point of sale tranasction table. The transaction table includes FK
links to employee, course, and session; with specific fields such as
supervisor, home department, registration, attendance, completion, and
certification. This way it is possible to report over the long term, all
attendance, and to indicate which supervisor and department employee reported
to at the time each course was taken.

If your personnel do not "move" around in the organization then this would
not be an issue!
"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
training courses.

Each course will come under a general heading ('coursetype') and there
will
be many courses ('coursename') for each type. Also, more than one
course
will
have a supervisor ('trainerInit').

At the moment I have five tables:

tble_employee

EmployeeID (PK)
Fname
Sname
Operations
Workstatus
Dept
SubDept

tble_course

EmployeeID (FK)
CourseType
CourseName (FK)
Trainingdate

I also have three other tables:

tble_crsetype_lkup

Coursetype(PK)

tble_crsename_lkup

Coursetype (FK)
Coursename (FK)

tble_CrseInit_lkup

CourseInit
Coursename (PK) (back to "tble_course" and "tble_crsename_lkup")

This database is in the very first stages of development and these are
the
fields I want to have at the moment.

I want to use "tble_crsetype_lkup" and "tble_crsename_lkup" as lookup
tables
to insert information into "tble_employee".

When creating a query listing the fields from "tble_employee" and
"tble_course" I also want to list the Supervisors Initials as well.

I hope this can be followed.

Does this sound okay?

I do not see why this would be a concern. The person who conducts the
training is linked to the record that contains the training session or
course information. Parameter queries, report grouping, and other means can
be used to filter and arrange the records as needed. If it is necessary to
keep track of past supervisors, job titles, etc. then that information
should be stored in a separate History table rather than as part of the
employee's record. I do not understand your situation, in part perhaps
because I do not connect with the comparison to a POS transaction table.
You mentioned movement within the organization. What happens if an employee
gets promoted to Supervisor? What becomes of that employee's training
records in the pre-promotion days? Do you create a parallel record in the
Supervisor table? If you have a Yes/No field for Supervisor in the employee
table you can filter by that field to produce a listing of supervisors.

"JR Hester" wrote in message
...
One issue to consider in adding the supervisor field to the employee
table:
IS it possible that an employee might, over the course of employment, have
a
differnt supervisor. Might it be necessary at some point to report data
based
on an employee's supervisor at the time a course was taken. Setting the
supervisor in teh employee table would almost certainly limit you to
showing
data based on CURRENT supervisor only. I.e. if am empoyee is assigned to a
new supervisor then all courses taken by that empoyee are now linked to
the
current supervisor.

I have a similiar situation, and used a "participant TRansaction table"
much
like a point of sale tranasction table. The transaction table includes FK
links to employee, course, and session; with specific fields such as
supervisor, home department, registration, attendance, completion, and
certification. This way it is possible to report over the long term, all
attendance, and to indicate which supervisor and department employee
reported
to at the time each course was taken.

If your personnel do not "move" around in the organization then this would
not be an issue!
"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
training courses.

Each course will come under a general heading ('coursetype') and
there
will
be many courses ('coursename') for each type. Also, more than one
course
will
have a supervisor ('trainerInit').

At the moment I have five tables:

tble_employee

EmployeeID (PK)
Fname
Sname
Operations
Workstatus
Dept
SubDept

tble_course

EmployeeID (FK)
CourseType
CourseName (FK)
Trainingdate

I also have three other tables:

tble_crsetype_lkup

Coursetype(PK)

tble_crsename_lkup

Coursetype (FK)
Coursename (FK)

tble_CrseInit_lkup

CourseInit
Coursename (PK) (back to "tble_course" and "tble_crsename_lkup")

This database is in the very first stages of development and these
are
the
fields I want to have at the moment.

I want to use "tble_crsetype_lkup" and "tble_crsename_lkup" as
lookup
tables
to insert information into "tble_employee".

When creating a query listing the fields from "tble_employee" and
"tble_course" I also want to list the Supervisors Initials as well.

I hope this can be followed.

Does this sound okay?