Hi all
Please refer to the db attached. Before I ask the question, here are the details of the db (and apologies for a long thread):
What I am looking to do is to highlight the records in EmployeeRoleTraining subF based on the fact whether a particular mandatory course was delivered or not (i.e., if a record for the CourseID exists in EmployeeXCourse_JT or not). If delivered, that particular entry should be green. If CourseID from EmployeeRoleTraining is not present in the EmployeeXCourse_JT, then that record should be highlighted red.
In the hope of doing so, I have created another query called EmployeeRoleTraining_SubQ which links EmployeeRoleTraining with EmployeeXCourse but I can clearly see that query does not produce right results.
Your help will be greatly appreciated.
Please refer to the db attached. Before I ask the question, here are the details of the db (and apologies for a long thread):
- CourseListT, EmployeeT, PositionT, and ProviderT are self-explanatory
- Course_JT (PK DeliverID) keeps records of courses provided by providers e.g., one course may have many providers
- RoleXCourse_JT keeps records of mandatory courses based on roles i.e., PositionID and CourseID
- RoleXEmployee_JT keeps records of which roles have been assigned to which employee i.e., PositionID and CourseID
- EmployeeXCourse_JT keeps records of which employee has been delivered which training (irrespective of whether that course is mandatory or not) i.e., course and provider both, hence contains EmployeeID and DeliverID
What I am looking to do is to highlight the records in EmployeeRoleTraining subF based on the fact whether a particular mandatory course was delivered or not (i.e., if a record for the CourseID exists in EmployeeXCourse_JT or not). If delivered, that particular entry should be green. If CourseID from EmployeeRoleTraining is not present in the EmployeeXCourse_JT, then that record should be highlighted red.
In the hope of doing so, I have created another query called EmployeeRoleTraining_SubQ which links EmployeeRoleTraining with EmployeeXCourse but I can clearly see that query does not produce right results.
Your help will be greatly appreciated.