Table Design

[Matt]

I have a table with too many fields and im wondering what some of your ideas
are. I am tracking employee training and there is a field for enrollment
date, completion date, comments, etc. The problem is that there are over 20
classes. So i have fields setup like this and i know there is a better way.

Enrollment_date_intro
Enrollment_date2_Inter
Completion_date1_Intro
Completion_date_Inter

and so on and so forth.

Another question i have is that there are 4 different types of progressions
for employees so I have 4 different tables with different courses (some
courses are the same). How should I relate the tables so that if an employee
transfers, some of the courses completed will transfer with the employee?

 

[KARL DEWEY]

You need a table that just contains employee information and has a primary
key field, preferable an autonumber field.
Next a table of courses with a primary key field.
Third, have a junction table that has the many side of a one-to-many
relationship from the employee table and from the course table. In the
junction table have the enrollment, completion, Pass/Fail, etc. fields.
Use form/subform to enter or view the data. If entering courses for
employee have employee in the main form and the junction table in the subform
with combox to select the courses available.

 

[Matt]

My two tables are related by the employee ID which is the pk in both tables.
Wouldnt this be a one-one relationship? Employee table and course table are
only related by employee ID.