DATABASE DESIGN

[Pam]

--
Pam
I want to use a subform on my personnel form to track training for
department personnel. I need something that can track training eveloutions,
instructor and total hours of training achieved by each individual. I
already have a subform on my personnel data form but I can't seem to make it
do what I need it to do. If anyone has a template for a training data base
that I could use as a strawman for developing a more efficient design, I
would be grateful. Thanks in advance.

 

[BruceM]

The place to start is with the database design. In a typical training
database you would have a Course table and an Employee table, plus a third
table to serve as a junction table. Each course can be attended by many
employees, and each employee can attend many courses. This is a
many-to-many relationship, so you need a junction table to resolve the
relationship. The tables may be something like this:

tblCourse
CourseID (primary key, or PK)
CourseDescription
Instructor
CourseDate
etc.

tblEmployee
EmployeeID (PK)
FirstName
LastName
etc.

tblEnrollment (junction table)
EnrollmentID (PK)
CourseID (foreign key, or FK)
EmployeeID (FK)

In the Relationships window, drag EmployeeID (tblEmployee) and CourseID
(tblCourse) onto the like-named fields in tblEnrollment. Click Enforce
Referential Integrity each time.
Create a form (frmCourse) based on tblCourse, and another (fsubEnrollment)
based on tblEnrollment. On fsubEnrollment, create a combo box bound to
EmployeeID (that's EmployeeID in tblEnrollment, the subform's Record
Source). Use a query based on tblEmployee as the combo box Row Source.
When you create the query, make EmployeeID the first column. In the second
column you could have something like:
LastFirst: [LastName] & ", " & [FirstName]
Use the combo box property sheet to set the Column Count to 2, the Column
Widths to 0";1.5", and the Bound Column to 1. The Bound Column is the one
you will store.
Use the toolbox to draw a subform/subreport control on frmCourse. Set this
control's Source Object to fsubEnrollment, and set the Link Child and Link
Master fields to CourseID.
Now you can enter a course description into frmCourse, and by way of the
subform populate the course with employees.
This approach assumes you already have a table containing the Employee
information.
There are several factors that could complicate this. If there is a list of
courses such as would exist at a school, you need to take that into account.
Also, you may need a separate table for instructors.
Just a note that each time I say something like "set the Column Count" I am
referring to choices on the property sheet. To see a form's Property Sheet,
open the form in Design View and click View >> Properties. For a control,
click the control to select it, and use View >> Properties if the Property
Sheet is not already open. Poke around a bit and you'll probably get the
hang of how the Property Sheet works.
Post back with any specific questions.

 

[tedmi]

I would propose a modification to BruceM's design. A course can be given many
times by different instructors on different dates, and this information
should be kept separate from the general, unchanging description of the
course. Also, an instrcutor table is recommended. So the schema looks like
this:

Course table:
CourseID (PK)
CourseDescription
Prereqs

Instructor table:
InstructorID (PK)
FirstFname
LastName

Session table:
SessionID (PK)
CourseID
InstructorID
Place
CourseDate

Enrollment table:
EnrollmentID (PK)
EmployeeID
SessionID

Employee table as per BruceM

If you want to get fancy, you can add a Course-Instructor junction table
that records which instructors are qualified to teach which courses.

 

[BruceM]

Not every training database relies on a course catalog approach to training
sessions. Where I work most training is a one-time event as prcedures are
updated or new procedures and processes are implemented. There is some
recurring information such as records of periodic meetings, which are
included with training because they have instructional elements, but these
are so different from one meeting to another that the only thing they have
in common is the general description.
When I suggested a design I pointed out that if there is a group of standard
courses it would affect the approach. I didn't want to get into a lot of
"what if" information without knowing more about the project, especially
since I had already written a fair amount, so I appreciate your filling in
the gap for the "course catalog" contingency.

 

[Amy Blankenship]

I would propose a modification to BruceM's design. A course can be given
many
times by different instructors on different dates, and this information
should be kept separate from the general, unchanging description of the
course. Also, an instrcutor table is recommended. So the schema looks like
this:

Course table:
CourseID (PK)
CourseDescription
Prereqs

Instructor table:
InstructorID (PK)
FirstFname
LastName

Session table:
SessionID (PK)
CourseID
InstructorID
Place
CourseDate

Enrollment table:
EnrollmentID (PK)
EmployeeID
SessionID

Employee table as per BruceM

If you want to get fancy, you can add a Course-Instructor junction table
that records which instructors are qualified to teach which courses.

If your courses are taught by people who are also employees, the
InstructorID field in the Session table should point to the Employee table,
not a separate table.

-Amy

 

[BruceM]

It gets a bit murky when the instructor may be either an employee or
somebody from outside the company, which was my situation. In the end I
decided to store the name. Many instructors are one-time only, so creating
an instructor table, then somehow using a query or something to combine it
with the employee table while maintaining some sort of key field, seemed
more complex than was needed. I'm storing data redundantly, but it seemed
to be one of those times when breaking a rule was the best choice.