Training Database

[Guest]

I'm trying to create a training database. I created tables for the standard
details ie name, address etc and the problem I encounter is when I come to
the courses tables.
Because there are many courses by different companies I created tables for
Training Company, Course Title, Date the course was taken, when it expires,
if they passed etc. For each table I assumed because there could be multiple
entries there would have to be multiple fields, so each table has 45 fields,
ie TrainingCompany1, ...2. etc.
I assume this is the wrong way to do it because I cannot create a report to
list all courses , descriptions , dates etc because there are too many fields
(45 x 6 plus the info of who it is). So how do i solve this problem, bearing
in mind I would like to view all courses on some kind of form that will
enable me to ammend any details that are wrong, plus create a report for each
person. Please can any one help.

 

[Allen Browne]

The crucial thing is that students do not enrol in a unit. A unit is
(potentially) offered many times over the years, so students enrol in an
instance of a unit (a particular course.)

You probably need tables something like this.

TrainingCo table (one record for each training company.) Fields:
TrainingCoID primary key
TrainingCo Text full name of this training company
...

Learner table (one record for each person who enrols in a course):
LearnerID primary key
Surname Text
FirstName Text
...

Unit table (one record for each unit that can be offered):
UnitID primary key
UnitName Text full name of this unit.
...

Course table (one record each time a unit is offered):
CourseID primary key
UnitID relates to Unit.UnitID
StartDate Date/Time when this instance of the unit
begins.
EndDate Date/Time when this instance ends
ExpireDate Date/TIme when people will need retraining.
TrainingCoID relates to TrainingCo.TrainingCoID
...

Enrol table (one record for each person enrolled in a course):
LearnerID relates to Learner.LearnerID
CourseID relates to Course.CourseID
EnrolDate when this person enrolled in this course.

 

[Guest]

Thanks for the reply Allen but I'm not sure exactly sure on your definition
of a unit.
We have training courses that can be taken at any time and by anyone. So
person A can take course a, b and c one week , and person b could take course
b & c another week. Plus there are refresher courses which can be attended by
some one week and others another week (so that we still have a workforce and
not everyone is away on a course). Does this change your help comments ?
Moving on to the tables, at this point I must add the fact that this
database has personnel records involved but mostly its a training database.
So if I start with an Employee Table that looks like.

NationalInsuranceNumberID - Primary Key
Surname
Forename
etc etc (all other fields that have one entry for each person)

Training Company Table :-

TrainingCompanyID - Primary Key Autonumber
NationalInsuranceNumberID - linked field ?
TrainingCompany
CourseDescription
CourseStartDate
CourseEndDate
CourseExpiryDate

Would this be the right way ie TrainingCompany not TrainingCompany1,
TrainingCompany2 etc. If this is so how can I show all of one person's
courses with dates etc on one form so its in a list format and be able to
change any typing mistakes.
I would also want to have other multiple entry forms because not only
do they do courses but when regulations change they also received updated
safety booklets (rev1 rev2 etc) which they must be sign for to acknowledge
its been received and this info has to be recorded for audits.
If you can reply with your comments I would be gratefull.
(ps. I have created the database fully - but have gone down the lines of
creating 45 fields for Training Company, 45 for a description etc etc and
this doesn't work. I have managed to create union queries, reports based
on expiry dates, but I dont think I've grasped the concept of how to design
tables that can store multiple entries for a single field for a single
person.)

 

[Allan Murphy]

Tim

Is this work related ?

I have created a training database for our work. It allows the staff to
nominate their training requirements. When the staff complete a training
course it is recorded. A report can be generated to show when staff who have
completed a course are due to refreshes as the course expiry date is soon
etc.

If you require more info please reply to me "off list"

Allan Murphy
Email: (e-mail address removed)

 

[Allen Browne]

Answers embedded.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the reply Allen but I'm not sure exactly sure on your
definition
of a unit.
We have training courses that can be taken at any time and by anyone. So
person A can take course a, b and c one week , and person b could take
course
b & c another week. Plus there are refresher courses which can be attended
by
some one week and others another week (so that we still have a workforce
and
not everyone is away on a course). Does this change your help comments ?
Moving on to the tables, at this point I must add the fact that this
database has personnel records involved but mostly its a training
database.
So if I start with an Employee Table that looks like.

NationalInsuranceNumberID - Primary Key
Surname
Forename
etc etc (all other fields that have one entry for each person)

Your Employee table looks good.

Training Company Table :-

TrainingCompanyID - Primary Key Autonumber
NationalInsuranceNumberID - linked field ?
TrainingCompany
CourseDescription
CourseStartDate
CourseEndDate
CourseExpiryDate

Your Training Company table is wrong unless never ever reuse a training
company again. It seems likely that one training company will teach multiple
courses at different times, so there is a one-to-many relation between
training companies and courses. This implies that you *must* have 2 tables,
with a one-to-many relation.

Do your training courses consist of groups of people, with a lecturer (or at
least a co-ordinator) assigned to each group? If so, you do have multiple
instances of each course, i.e. a particular course can be run on multiple
occassions. If this is so, you have a one-to-many relation between the
course codes and the actual course instances. You therefore need one table
of the possible course codes (what I called "units"), and another table of
the actual course instances (occasions when a unit is taught.) Every
training database needs to be set up this way, unless there are no groupings
at all. (I guess that might happen if you have students doing unsupervised
web courses where they attend no classes, no co-ordinator, and they can
enrol in anything at any time.)

Refresher courses are "units" in their own right (i.e. they have different
pre-requisites, content, duration, and possibly expiry than the original.)

Would this be the right way ie TrainingCompany not TrainingCompany1,
TrainingCompany2 etc. If this is so how can I show all of one person's
courses with dates etc on one form so its in a list format and be able to
change any typing mistakes.

If you follow through the structure I originally suggested, you can create a
main form bound to the Learner table (your Employee table), and a subform
bound to the Enrol table. This subform shows all the courses the person has
taken.

I would also want to have other multiple entry forms because not only
do they do courses but when regulations change they also received updated
safety booklets (rev1 rev2 etc) which they must be sign for to acknowledge
its been received and this info has to be recorded for audits.

This is introducing lots of other tables as well: a Role table (the possible
roles an employee can have), an EmployeeRole table (the actual roles each
employee has), a Standard table (records of the standards that need to be
met), a StandardRole table (defining which standards need to be met for each
role), a UnitStandard table (that defines which standards are covered by
studing each unit.) From that you can design queries that tell you what
standards apply to each employee, and you can the query the rest of the
database to see if the employee has completed the courses that give them
accrediation that they have met each of the standards that apply to them.

But please: I strongly suggest you understand the tables for the other
basics down before adding this extra level of complexity to your design.

If you can reply with your comments I would be gratefull.
(ps. I have created the database fully - but have gone down the lines of
creating 45 fields for Training Company, 45 for a description etc etc and
this doesn't work. I have managed to create union queries, reports based
on expiry dates, but I dont think I've grasped the concept of how to
design
tables that can store multiple entries for a single field for a single
person.)

Precisely correct. It is completely the wrong track to use 45 fields for 45
training companies. As you seem to understand now, you need to use multiple
tables with one-to-many relationships instead of multiple fields.

It all boils down to *thinking* one-to-many.