I have been reading and from Bob's example database I modified it slightly so that the table fields are as follows.
Then I created a query as follows:-
Code:
SELECT tblEmp.EmpID, tblDept.Dept, tblJobs.JobTitle, [FName] & " " & [LName] AS Employee, tblEmp.Active, tblCerts.Cert, tblEmpCerts.DateCompleted, tblCerts.ValForMonths, DateAdd("m",[ValForMonths],[DateCompleted]) AS ExpiresFROM ((tblDept RIGHT JOIN tblEmp ON tblDept.DeptID = tblEmp.DeptID) LEFT JOIN tblJobs ON tblDept.DeptID = tblJobs.DeptID) LEFT JOIN (tblCerts RIGHT JOIN tblEmpCerts ON tblCerts.CertID = tblEmpCerts.CertID) ON tblEmp.EmpID = tblEmpCerts.EmpID
WHERE (((tblEmp.Active)=True));
This produces the following results:-