Hi all
In my training db, I have CourseListT and Course_JT tables. CourseListT contains CourseID (PK), CourseName, and ValidFor fields. Course_JT contains DeliverID (PK), CourseID (FK), ProviderID (FK), and other fields. This is to handle the situation where any course may be delivered by more than one providers. The way I have linked the delivery of each training to an employee is by using an EmployeeXCourse_JT which contains ID (PK), EmployeeID (FK), CourseID (FK), and CertificationDate.
Issue arises when I try to combine this data in one query i.e., TrainingRecordQ (see the screenshot below). One of the courses, CourseID = 198 has 3 records in Course_JT, however, only one of them is currently assigned to an employee in EmployeeXCourse_JT (to EmployeeID = 52). However, the following query returns 2 extra records i.e., it assigns EmployeeID = 52 to other two CourseID = 198 as well, with the same CertificationDate and displays it in the outcome. The EmployeeXCourse_JT has a total of 21 records as for now but this query produces 23 records. How do I eliminate this error?
I tried to fix this by making another query, CourseQ (which itself outputs CourseListT and Course_JT) and joined it with EmployeeXCourse_JT with "Include ALL records from 'EmployeeXCourse_JT' and only those records from 'CourseQ' where the joined fields are equal" but to no avail.
I will really appreciate if someone could suggest what is going wrong here and why, so I can fix this problem and not repeat it in the future.
Regards
In my training db, I have CourseListT and Course_JT tables. CourseListT contains CourseID (PK), CourseName, and ValidFor fields. Course_JT contains DeliverID (PK), CourseID (FK), ProviderID (FK), and other fields. This is to handle the situation where any course may be delivered by more than one providers. The way I have linked the delivery of each training to an employee is by using an EmployeeXCourse_JT which contains ID (PK), EmployeeID (FK), CourseID (FK), and CertificationDate.
Issue arises when I try to combine this data in one query i.e., TrainingRecordQ (see the screenshot below). One of the courses, CourseID = 198 has 3 records in Course_JT, however, only one of them is currently assigned to an employee in EmployeeXCourse_JT (to EmployeeID = 52). However, the following query returns 2 extra records i.e., it assigns EmployeeID = 52 to other two CourseID = 198 as well, with the same CertificationDate and displays it in the outcome. The EmployeeXCourse_JT has a total of 21 records as for now but this query produces 23 records. How do I eliminate this error?
I tried to fix this by making another query, CourseQ (which itself outputs CourseListT and Course_JT) and joined it with EmployeeXCourse_JT with "Include ALL records from 'EmployeeXCourse_JT' and only those records from 'CourseQ' where the joined fields are equal" but to no avail.
I will really appreciate if someone could suggest what is going wrong here and why, so I can fix this problem and not repeat it in the future.
Regards