Creating an Employees' Training DB - Advice on Design

[biganthony via AccessMonster.com]

Hello,

My workplace have asked me to look at creating a database to keep track of
staff training/ professional development.They currently keep it all in an
Excel spreadsheet and mail merge into a Word transcript that lists all
courses completed during the year.

These are the rules used:
* All employees must attend some form of training. Most of the time, this
training session occurs "in-house" and occurs for one hour each week.
* Some staff may attend courses run by an external organisation. These
external courses are run off-site and may run for a day, an hour, a few hours
or a week etc.
* All employees can run a session and teach their peers during the in-house
training. (For example, on offfice lady decided to run a session on Mail
Merge to other employees.) So, these employees teaching others are
instructors for that class. (The following week, they may in fact be learning
from another peer.)
* A transcript is provided at the end of the year that lists the courses
employees attended (internal and external) and lists any courses that they
have run themselves.
* There are some courses that must be done every year eg fire and safety and
OH&S.

Below I have attempted to normalise the data.

EMPLOYEE Table:
EmployeeID (PK)
LastName
FirstName
JobTitle


COURSE Table
CourseID (PK)
CourseName
CourseCategory (Internal or Externally provided)


COURSEINSTRUCTOR Table
CourseInstructorID (PK)
CourseID (FK)
TrainingCompanyID (FK) (for external training companies)
InstructorID (FK) (for internal trainer)


INSTRUCTOR Table (this would be those employees who are running their
own courses)
InstructorID (PK)
EmployeeID (FK)


SESSION Table
SessionID (PK)
CourseID (FK)
CourseInstructorID (FK)
StartDate
EndDate
CourseFee
CourseLength
Location


ENROLMENT Table
EnrolmentID (PK)
EmployeeID (FK)
SessionID (FK)


TRAININGCOMAPANY Table (one record for each external training company.)
TrainingCompanyID (PK)
TrainingCompanyName
TrainingCompanyAddress


Where I think I am getting confused is that the employee can attend a
training session where the instructor is one of their peers, or they may go
to an external training session, where the instructor is from an external
training company. In the former case, the InstructorID for that session would
point to an EmployeeID. In the example above, I have used the term
'Instructor' to refer to the employee who is leading a course and the
training company refers to an external organisation that provides training.

Am I on the right track? I would appreciate any advice.

Thanks,
Anthony

 

[Jeff Boyce]

Your first table puts a job title together with an employee. Does that mean
your employees always and only have a single job title at any time (no
"split" duties)? Does that mean you aren't interested in what job title an
employee held when s/he took training (i.e., no "history")? If you have
more than one employee with the same job title, you'll be repeating the "Job
Title" text in every matching employee record.

Right at the start, consider using a tblEmployee, tblJobTitle, and
trelEmployeeJob. That third table would be used to resolve a "many-to-many"
relationship, if you have one (or could, in the future) between employees
and job titles. And if you go that route, then I suspect it would be the
trelEmployeeJob.EmployeeJobID that you'd show as having attended training,
so you could keep a history of who was in what job when s/he went to what
training.

Just a start...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Michael Gramelspacher]

Hello,

My workplace have asked me to look at creating a database to keep track of
staff training/ professional development.They currently keep it all in an
Excel spreadsheet and mail merge into a Word transcript that lists all
courses completed during the year.

These are the rules used:
* All employees must attend some form of training. Most of the time, this
training session occurs "in-house" and occurs for one hour each week.
* Some staff may attend courses run by an external organisation. These
external courses are run off-site and may run for a day, an hour, a few hours
or a week etc.
* All employees can run a session and teach their peers during the in-house
training. (For example, on offfice lady decided to run a session on Mail
Merge to other employees.) So, these employees teaching others are
instructors for that class. (The following week, they may in fact be learning
from another peer.)
* A transcript is provided at the end of the year that lists the courses
employees attended (internal and external) and lists any courses that they
have run themselves.
* There are some courses that must be done every year eg fire and safety and
OH&S.

Below I have attempted to normalise the data.

EMPLOYEE Table:
EmployeeID (PK)
LastName
FirstName
JobTitle


COURSE Table
CourseID (PK)
CourseName
CourseCategory (Internal or Externally provided)


COURSEINSTRUCTOR Table
CourseInstructorID (PK)
CourseID (FK)
TrainingCompanyID (FK) (for external training companies)
InstructorID (FK) (for internal trainer)


INSTRUCTOR Table (this would be those employees who are running their
own courses)
InstructorID (PK)
EmployeeID (FK)


SESSION Table
SessionID (PK)
CourseID (FK)
CourseInstructorID (FK)
StartDate
EndDate
CourseFee
CourseLength
Location


ENROLMENT Table
EnrolmentID (PK)
EmployeeID (FK)
SessionID (FK)


TRAININGCOMAPANY Table (one record for each external training company.)
TrainingCompanyID (PK)
TrainingCompanyName
TrainingCompanyAddress


Where I think I am getting confused is that the employee can attend a
training session where the instructor is one of their peers, or they may go
to an external training session, where the instructor is from an external
training company. In the former case, the InstructorID for that session would
point to an EmployeeID. In the example above, I have used the term
'Instructor' to refer to the employee who is leading a course and the
training company refers to an external organisation that provides training.

Am I on the right track? I would appreciate any advice.

Thanks,
Anthony

Probably the Instructors table needs to be something like this:

CREATE TABLE Instructors (
InstructorID LONG NOT NULL,
InstructorType CHAR(1) NOT NULL,
CHECK (InstructorType IN ('I','E')), -- implement as validation rule
PRIMARY KEY (InstructorID,InstructorType));

So the InstructorID can come from either Employees or Companies.

SELECT Employees.EmployeeID,
[LastName] & (", " + [FirstName]) AS FullName,
"I" AS InstructorType
FROM Employees
UNION
SELECT Companies.CompanyID,
Companies.CompanyName,
"E" AS InstructorType
FROM Companies
ORDER BY FullName;

This would be the rowsource of a combobox on the instructor form. There would also be
a textbox to hold InstructorType.

In the AfterUpdate Event of the InstructorID combobox you would assign
txt_InstructorType = cbo_InstructorID.Column(2)

Just consider this as an untested idea.

 

[biganthony via AccessMonster.com]

Thnaks Jeff and Michael.

I also have looked at the classroom management template from MS as well as
examples from this newsgroup.

Will see how I go with the Instructor table and data etc.

Anthony

Hello,

[quoted text clipped - 76 lines]

Thanks,
Anthony

Probably the Instructors table needs to be something like this:

CREATE TABLE Instructors (
InstructorID LONG NOT NULL,
InstructorType CHAR(1) NOT NULL,
CHECK (InstructorType IN ('I','E')), -- implement as validation rule
PRIMARY KEY (InstructorID,InstructorType));

So the InstructorID can come from either Employees or Companies.

SELECT Employees.EmployeeID,
[LastName] & (", " + [FirstName]) AS FullName,
"I" AS InstructorType
FROM Employees
UNION
SELECT Companies.CompanyID,
Companies.CompanyName,
"E" AS InstructorType
FROM Companies
ORDER BY FullName;

This would be the rowsource of a combobox on the instructor form. There would also be
a textbox to hold InstructorType.

In the AfterUpdate Event of the InstructorID combobox you would assign
txt_InstructorType = cbo_InstructorID.Column(2)

Just consider this as an untested idea.

 

[Michael Gramelspacher]

Thnaks Jeff and Michael.

I also have looked at the classroom management template from MS as well as
examples from this newsgroup.

Will see how I go with the Instructor table and data etc.

Anthony

I do not think what I suggested earlier will work. I think this is the correct design for
Instructors.

CREATE TABLE Instructors (
InstructorID IDENTITY(1,1) NOT NULL,
InstructorType CHAR(1) NOT NULL,
CHECK (InstructorType IN ('I','E')),
PRIMARY KEY (InstructorID,InstructorType));

CREATE TABLE InstructorsInternal (
InstructorID IDENTITY(1,1) NOT NULL,
InstructorType CHAR(1) DEFAULT 'I' NOT NULL,
CHECK (InstructorType ='I'),
EmployeeID INTEGER NOT NULL
REFERENCES Employees (EmployeeID),
FOREIGN KEY (InstructorID,InstructorType)
REFERENCES Instructors (InstructorID,InstructorType),
UNIQUE (EmployeeID),
PRIMARY KEY (InstructorID));

CREATE TABLE InstructorsExternal (
InstructorID INTEGER NOT NULL,
InstructorType CHAR(1) DEFAULT 'E' NOT NULL,
CHECK (InstructorType ='E'),
CompanyID INTEGER NOT NULL
REFERENCES TrainingCompanies (CompanyID),
FOREIGN KEY (InstructorID,InstructorType)
REFERENCES Instructors (InstructorID,InstructorType),
UNIQUE (CompanyID),
PRIMARY KEY (InstructorID));

I have never implemented a subtypes structure before. My thought would be
to make Instructors the main form and either InstructorsInternal or InstrucrorsExternal
the subform. I think I would use the AfterInsert Event to switch the Subform SourceObject
between subforms, depending upon whether "I" or "E" is in the main form.

Maybe one of the experts can jump in with something easier.

 

[Shon]

Hello,
I'm in a similar position with having to develop some type of training db
for my company. I must admit I'm not the most enthusiastic MS Access person
but have been trying to work my way through. I've taken a few classes but the
information covered was not relevant to what I need. It seems so complicated
building the different tables and then making sure everything is linked
together. I that something was mentioned about classroom management. Where
can I find out more about that?

Any and all help greatly appreciated!

 

[biganthony via AccessMonster.com]

Shon,

The classroom management template is available from the MS template site. The
link is:

http://office.microsoft.com/en-au/templates/TC010184071033.aspx?CategoryID=CT101426031033


Regards
Anthony

Hello,
I'm in a similar position with having to develop some type of training db
for my company. I must admit I'm not the most enthusiastic MS Access person
but have been trying to work my way through. I've taken a few classes but the
information covered was not relevant to what I need. It seems so complicated
building the different tables and then making sure everything is linked
together. I that something was mentioned about classroom management. Where
can I find out more about that?

Any and all help greatly appreciated!

Hello,

[quoted text clipped - 76 lines]

Thanks,
Anthony