Database Architecture Suggestions

[AccessPower]

I have a database in the works that is centered around training for employees. I am trying to develop a database that is optimally functional. I am debating on what I should do for the architecture of the tables and their relationships. Here is the criteria:

Employee information must be contained within a table of its own.
Course information must be contained within a table of its own.

The job code of the employee falls under an umbrella job group. Whether or not a course is mandatory is based on the job group.

There also needs to be a way to track what courses have been done by each employee, a way to pull up courses that are due within the next three months for each employee, and a way to see what training hasn't been completed or is overdue. Course requirement intervals can be Annual, 2 Year, 3 year, and 5 Year.

All courses have CourseID.
All employees have EmployeeID
All Jobs have JobID


All Job Groups have JobGroupID

Can anyone suggest an efficient architecture for this?

Thanks!

[ranman256]

a subtable to tEmployee table, will be tEmpTraining.
EmployeeID, CourseID, DateTaken

this way , you can lookup their training and get the Course most recent training.
you need a tCourses ExpLength (2 years, 3 years) to hold the limit on this training.

combine the tCourses ExpLength and tCourses.CourseID joined to tEmpTraining.CourseID and get DateTaken and calculate the ExpDate field:
ExpDate: DateAdd("yyyy",tCourses ExpLength, tEmpTraining.DateTaken