I have a Query and a Table. The Query "EmployeeTraining" includes the employee name, and the training course they have taken, date taken and expiry. What I want the LeftJoin to return is ALL the training courses available and the courses that each employee is missing, so in essense, any nulls.
In total there are 5 records in "ApplicableTraining". Some employees have taken only 4/5 of the courses and only has 4 records in the "EmployeeTraining" record. Shouldn't left join return the 5th training item for that particular employee with nulls in the "datetaken" and "Expiry"?
Sample Applicable Training:
FROM ApplicableTraining LEFT JOIN EmployeeTraining ON ApplicableTraining.AppTraining = EmployeeTraining.Training;
You need to do CROSS JOIN between table:Employee and table:ApplicableTraining after LEFT JOIN with view:EmployeeTraining.
CROSS JOIN, join all records from table A with table B, regardless of connection between their tables.
Sample Applicable Training: WHMIS First Aid CPR Propane TDG Sample Employee SAM Result: SELECT ApplicableTraining.AppTraining, Table[Employee].Employee FROM ApplicableTraining CROSS JOIN Table[Employee] WHMIS|SAM First Aid|SAM CPR|SAM Propane|SAM TDG|SAM Desired Results SELECT Table[Employee].Employee, ApplicableTraining.AppTraining, EmployeeTraining.DateTaken, EmployeeTraining.Expiry FROM ApplicableTraining CROSS JOIN Table[Employee] LEFT JOIN EmployeeTraining ON ApplicableTraining.AppTraining = EmployeeTraining.Training and EmployeeTraining.Employee = Table[Employee]."EmployeeID" Sam | WHMIS | 05/03/2011 |05/03/2012 Sam | First AID | 06/09/2010 | 06/09/2011 Sam | CPR | 05/03/2011 | 05/03/2012 Sam | Propane | 12/03/2015| 12/03/2018 Sam | TDG | |