Query Construct

[zburns]

Thanks in advance - your help is greatly appreciated.

I need my query to pull all certifications and employees that have received them, even those certifications that an employee does not have. Essentially showing in a report (grouped by emp) all certifications each emp has and those not yet received as evidenced by no Certification date.


I have three tables: see attachment

Not sure how to construct this to produce desired records
SELECT [LastName] & ", " & [FirstName] & " " & [MiddleInitial] AS EmpName, tbl_Certifications.CertName, tbl_CertDetails.CertIssueDate
FROM tbl_Employees INNER JOIN (tbl_Certifications INNER JOIN tbl_CertDetails ON tbl_Certifications.CertID = tbl_CertDetails.CertID) ON tbl_Employees.EmployeeID = tbl_CertDetails.EmpID;

[June7]

I presume tbl_CertDetails has records where employee has cert, no record if employee does not have a cert.

What you need is a dataset of all possible pairings of employee and certs. This can be achieved with a query that includes tbl_Employees and tbl_Certifications. There will not be a JOIN clause and the result will be a Cartesian relation - all records from each table will join with all records of other table - giving all possible pairs.

Now join that dataset with tbl_CertDetails by compound link on EmpID and CertID fields, join type "Include all records from {Cartesian dataset} ...".

[zburns]

That's exactly what I needed. Worked perfect.
Thank you very much