The courses are set up for the managers, beginners are level one, intermediate are level 2, department heads are level 3, etc. (currently there are 5 levels). There are several courses offered to the different levels. For example, level 1 offers three courses, level 2 offers 4 courses. Because the courses are not scheduled in the order that each manager may need, we would like to be able to see which managers need the course when they become available. For example, if course number 46592 were scheduled, the query would display everyone that needs to take the course. Course #46592 will only be present in one level. It is possible be a 2nd level manager that never received one or more 1st level courses, that person should be in the list. The list should display any person equal to or below the training level and did not complete the course. There is one exception, the managers can be waived a course. For example, a manager comes on board at level 4; they will not have to complete level 1 course there status would be waived. They also would not be in the list. The statuses are complete, incomplete, waived, and in cases where the person never took the class the status should be blank. So when we list the records we should only see people at or below the training level with an incomplet or blank status.
The tables are structure as:
TblManagers: The SEID is the primary key.
SEID LastName FirstName TrainingLevel
AAAA1 GRAY MARY FLRP/CDP
BBBB1 BLUE CINDY Manager
KJNCB DEAN STEPHANIE DM
TblCourseList: Level is the Foreign key from the trainingLevel table
CourseNumber Level Course
13458 1 Transition to Management Training (TMT) 13458
13466 1 Management Aspects of EEO (MAEEO) (Online) 13466
15344 2 Leading Teams 15344
15346 3 Administrative Procedures for Managers (APM) (Online) 15346
TblTrainingRecord: SEID is foreign key from TblManagers and CourseNumber is foreign key from TblCourseList
RecNumber AttendeeSEID CourseNumber FromDate ToDate Status
1 AAAA1 13458 1/2/2009 1/30/2009 COMPLETE
2 AAAA1 13466 1/30/2009 2/15/2009 INCOMPLETE
3 BBBB1 15341 1/30/2009 2/15/2009 WAIVED
4 BBBB1 15344 2/2/2009 2/19/2009 COMPLETE
5 AAAA1 15344 2/2/2009 2/19/2009 COMPLETE
6 BBBB1 15346 3/1/2009 3/3/2009 INCOMPLETE
** There is one problem with the above table; it should show a blank from date, to date and status for a person who hasn’t taken any course.
Hope this makes sense.