database setup

[kylenick]

Hi,

I'm new to access and I'm having difficulty setting up my training records
database. The purpose of this database is to monitor when an employee will be
moving up to the next labour grade (i.e. promoted). There are certain courses
offered within the organization that will enable an employee to move up to
the next labour grade. The tables that i have are called, "Training
Records," "Employee Records", and " Training Courses"

The table "Training Records" tracks all of the training courses that have
been completed, therefore, it's there will be duplicate entries. It contains
the following fields (column headings):
- Employee Name
- Employee Number
- Course Code
- Course Title
- Department #
- Date Completed

The "Employee Records" table only contains information related to an
employee. It has the following fields:
- Employee Number
- Employee Name
- Current Labour Grade
- Department

The final table is "Courses Available." This table contains all of the
courses that the organization offers. It has the following fields:
- Course Code
- Course Required to Acheive Labour Grade (This means that once all of the
courses with this code have been completed, they will move to the next labour
grade)
- Course Name
- Course Category (This just states which department offers the course and
is irrelavent)

Ultimately the goals is to be able to pull a report based on the employee
number to see which courses each employee has taken, and which courses are
still required to move to the next labour grade.

Any help regarding this project would be greatly appreciated. Thanks.

 

[Klatuu]

You are pretty close. One thing I see is that the Training Records table
does not need the employee name. It is redundant data. It only needs the
employee number to relate it to the employee table. Here is how I would
suggest you define your tables. Note: Spaces in names are bad.

This table will identify employees enrolled in a specific occurance of a
course. It will relate to tblCourseSchedule on CourseID. Many students may
be enrolled in one course.
tblCourseRegistration

- Employee Number
- CourseID
- CourseCompleted (Y/N)

This table Identifies a specific occurance of a course in which employees
may register. It will relate to tblCourse. One course may be scheduled many
times.
tblCourseSchedule

- CourseID
- CourseCode (The course being offered)
- CourseDate (The date the course is offered)

This table identifies employees that may enroll in courses. One employee
may enroll in may course schedules.

tblEmployee

- EmployeeNumber
- EmployeeName
- CurrentLabourGrade
- Department

This table describes the courses that are offered and whether the course is
required to more to the next labour grade. Once course may be scheduled many
times.

tblCourse

- CourseCode
- CourseRequiredToAcheiveLabourGrade (This means that once all of the
courses with this code have been completed, they will move to the next labour
grade)
- CourseName
- CourseCategory (This just states which department offers the course and
is irrelavent)

This will give you the greatest flexibility. The only thing missing from
this would be a table that defines the courses required to move to the next
grade. I can't define it because I don't know the rules. For example, If
an employee is at the first level, you would need to describe the courses
required to move to level 2, then the courses needed to move to level 3, etc.
But maybe that is what the CourseRequiredToAcheiveLabourGrade field in
tblCourse is used for. But how do you know all the courses required to move
from level 1 to level 2, for example?