Training records

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?

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 would not have a separate table for supervisors. Add a field to Employee
table for supervisor. In the relationship window add the Employee table
again and set a one-to-many relation from EmployeeID to Supervisor field.
The supervisor’s EmployeeID is therefore linked to the supervisor field of
many employees.

Add to the Course table - Requirement (OSHA, Management, etc.), Interval
(one-time [zero], monthly, annually-12 months, etc.). Use the lowest common
denominator for interval such as month or year. Use a zero if it is one-time
for the interval. You can then use a query to calculate the next due date by
calculating interval months after last class date for individual for
reoccurring training.

Your Course records needs ClassID, courseID, Instructor, date, etc.

You need an employee-course requirements table. I used an append query for
new employees so that the new employee had all requirement from their
section/department automatically added to them. The supervisor was asked to
delete any that did not apply to the individual.


"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?

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?

Below should read

tble_supervisor
SupID (PK)
Name
Initials


"scubadiver" wrote:

tble_supervisor
Name (PK)
Initials

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?

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
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?

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
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?

scubadiver,

Have a read through
http://msdn.microsoft.com/library/de...html/apg02.asp

which although is an adp / sql server app, may give some interesting
thoughts for you since it develops a similar database.

swas

"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

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