This query returns EmployeeID, DateCleared, and DateFitTested:
Code:
SELECT empl.EmployeeID, test1.DateCleared, test2.DateFitTestedFROM (tblDTHCEmployees empl
LEFT JOIN (SELECT test01.EmployeeID, test01.DateCleared FROM tblRespiratorFitTestData test01 WHERE test01.FTID IN (SELECT MAX(FTID) FROM tblRespiratorFitTestData WHERE EmployeeID = test01.EmployeeID AND DateCleared Is Not Null)) test1 ON test1.EmployeeID = empl.EmployeeID)
LEFT JOIN (SELECT test02.EmployeeID, test02.DateFitTested FROM tblRespiratorFitTestData test02 WHERE test02.FTID IN (SELECT MAX(FTID) FROM tblRespiratorFitTestData WHERE EmployeeID = test02.EmployeeID AND DateFitTested Is Not Null)) test2 ON test2.EmployeeID = empl.EmployeeID
WHERE empl.ReqYN = TRUE;
To get additional fields, you have to decide, from which subquery you must get them, add appropriate fields into this subquery, and in main query read this field from subquery. An example:
As data for a person are read from 2 different entries of table tblRespiratorFitTestData (cleared and fit-tested entries), you either must have 2 Remarks fields, or select one of them. Let's assume you want the remark for cleared entry displayed. You have to edit the query like
Code:
SELECT empl.EmployeeID, test1.DateCleared, test2.DateFitTested, test01.Remarks FROM (tblDTHCEmployees empl
LEFT JOIN (SELECT test01.EmployeeID, test01.DateCleared, test01.Remarks FROM tblRespiratorFitTestData test01 WHERE test01.FTID IN (SELECT MAX(FTID) FROM tblRespiratorFitTestData WHERE EmployeeID = test01.EmployeeID AND DateCleared Is Not Null)) test1 ON test1.EmployeeID = empl.EmployeeID)
LEFT JOIN (SELECT test02.EmployeeID, test02.DateFitTested FROM tblRespiratorFitTestData test02 WHERE test02.FTID IN (SELECT MAX(FTID) FROM tblRespiratorFitTestData WHERE EmployeeID = test02.EmployeeID AND DateFitTested Is Not Null)) test2 ON test2.EmployeeID = empl.EmployeeID
WHERE empl.ReqYN = TRUE;
There are some employees which don't have valid testing data in tblRespiratorFitTestData. For those employees all fields except EmployeeID (and any other fields read from tblDTHCEmployees) are empty.