On my Left Table I have all EMP Information joint with all possible Asset, SO all Employees are assigned to take all courses listed in the database. AND the Right Table I have Only Employees who has taken or in process of taking the course.( Both tables are queries and their results are correct)
I would like to do Left join between two tables (queries) so I get All Employees from Left-Table joint Right-Table with new columns (Completion date & Completion status)!
However, when I do left-join it returns all my rows and fills the blanks with the two possibilities for Completion Status which is Completed OR in Progress). The most stupid thing happens when I add Completion date and returns some random dates and fills all rows for Completion Dates and repeats those rows for following asset titles.
My result should be a list of all EMP joint with required courses with the status of course, this person has completed the course or not and I would like to get null for all those non related rows. Will you be kind and check my code and let me know what can cause this problem? Thank you
My code :
Code:
SELECT qryEmployeeCourse.[EMP ID], qryEmployeeCourse.Name, qryEmployeeCourse.Role, qryEmployeeCourse.Location, qryEmployeeCourse.Region, qryEmployeeCourse.[Asset ID], qryEmployeeCourse.[Asset Title], qryCourseStatus.[Completion Status]
FROM qryEmployeeCourse LEFT JOIN qryCourseStatus ON qryEmployeeCourse.[EMP ID] = qryCourseStatus.Username
GROUP BY qryEmployeeCourse.[EMP ID], qryEmployeeCourse.Name, qryEmployeeCourse.Role, qryEmployeeCourse.Location, qryEmployeeCourse.Region, qryEmployeeCourse.[Asset ID], qryEmployeeCourse.[Asset Title], qryCourseStatus.[Completion Status]
ORDER BY qryEmployeeCourse.Name, qryEmployeeCourse.Role, qryEmployeeCourse