With the help of Experts I finally got the following query working where it returned 1 record for each employer with a count of active employees, paidout employees and forfeiture employees. Data set returned contains 3 rows with 5 columns. So far so good until I discovered that my query for active employees was incorrect. In the current query, the WHERE clause is Employees.EmployeeStatus='
ACTIVE'. I need to change the query to be WHERE Employees.PlanStatus<>'PAI
D OUT' AND Employees.PlanStatus<>'FOR
FEITURE'. I thought this would be a simple change but when I made it, the data set returned contains 5 rows with 5 columns. The one employer has an employee with a PlanStatus = "PAID OUT" and another Employee with a PlanStatus = "FORFEITURE". As soon as I change the = to <> or add the second condition to the WHERE clause, more than one record is returned for the employer that has employees with different plan status. Any insight into this problem would be appreciated.
SELECT a.EmployerName, a.EmployerID, b.ActiveEmployees, c.PaidOutEmployees, d.ForfeitureEmployees
FROM ((employers AS a LEFT JOIN (SELECT Count(Employees.EmployeeStatus) AS ActiveEmployees, Employees.EmployerID FROM Employees WHERE Employees.EmployeeStatus='ACTIVE' GROUP BY Employees.EmployerID, Employees.EmployeeStatus) AS b ON a.EmployerID=b.EmployerID) LEFT JOIN (SELECT Count(Employees.PlanStatus) AS PaidOutEmployees, Employees.EmployerID FROM Employees WHERE Employees.PlanStatus='PAID OUT' GROUP BY Employees.EmployerID, Employees.PlanStatus) AS c ON b.EmployerID=c.EmployerID) LEFT JOIN (SELECT Count(Employees.PlanStatus) AS ForfeitureEmployees, Employees.EmployerID FROM Employees WHERE Employees.PlanStatus='FORFEITURE' GROUP BY Employees.EmployerID, Employees.PlanStatus) AS d ON c.EmployerID=d.EmployerID;