Building Training Tracker DB

[BrokenBiker]

Hello All,
I've been away from Access for quite a while, and unfortanely my brain is not wanting to work in Access-mode.

What I need the db to do is track all the course codes on each person. As training currently stands, not all the course codes are listed on each person. For example, one person may be trained on aircraft towing, but another person may not be. If it works out easier for this db to have all course codes 'loaded' against each person, that won't be a problem either. In fact, that's where I want to eventually go.

The db will also need to incorporate the next due date based on the course interval (in months) and the date the training was last completed.

The attached Excel file is an example of what I'd like the Access file to do, but my brain is stuck and can't seem to build the necessary query. And yes, I feel like a dunce....but once I get my brain rollin' in the right direction, I'll pick it all back up.

Where I need help is building a query to link the people and the course codes together. I might need to change the table design(s). Also, the Access file is 2007 and based off the Project Template.

I've been having troubles building a tracker in Excel, as Excel 2007 keeps trying to figure what I'm doing and it changes things whether I want it to or not.

 

[MarkK]

Hey there.
I see the problem has having four critical objects you need to keep track of.
1) Course - A table of course descriptions as they might appear in a catalog. This is the full set of courses that you offer, but doesn't include when or where and doesn't change year to year.

[B]tCourse
[/B]CourseID (PK)
CourseName

2) Class - A table of specific instances of a Course. This table specifies the date and location the class is offered, like Spring 2010, Room 214.

[B]tClass[/B]
ClassID (PK)
CourseID (FK)
StartDate/Semester/Scheduling information
RoomNumber

3) StudentClass - A table of the relationships between a single student and a single class. This is where the student's grades go and other data about one students participation in one class. This is also the link table in the many-to-many relationship between class and student. One student can take many classes - One class has many students.
Filter this table by ClassID to see a list of students for that class, or filter by StudentID to see a list of classes for that student.

[B]tStudentClass[/B]
StudentClassID (PK)
ClassID (FK)
StudentID (FK)
Performance/Attendance information

4) Student - Obvious.

[B]tStudent[/B]
StudentID (PK)
Name etc...

Your system appears to have tables 1 and 4 (CourseList and Employee)