Problem With WHERE Clause

[zephaneas]

I'm trying to add a check for Employees.IsActive to this query

SELECT d.GroupId, 
       Sum(d.cert_complete) AS SumOfcert_complete, 
       Count(d.cert_complete) AS CountOfcert_complete
    FROM (SELECT DISTINCT s.GroupId, e.EmployeeID, IIf(e.Certified Is Null,0,1) AS cert_complete 
    FROM ((Stores AS s 
    INNER JOIN EmployeeStores AS es ON s.StoreId=es.StoreId) 
    INNER JOIN Employees AS e ON es.EmployeeId=e.EmployeeID) 
    INNER JOIN Titles AS t ON e.TitleId=t.TitleId WHERE t.IsManager=True AND e.IsActive=True)  AS d
    GROUP BY d.GroupId
UNION ALL SELECT 0 as GroupId, 
       Sum(d.cert_complete) AS SumOfcert_complete, 
       Count(d.cert_complete) AS CountOfcert_complete
    FROM (SELECT DISTINCT s.GroupId, e.EmployeeID, IIf(e.Certified Is Null,0,1) AS cert_complete 
    FROM ((Stores AS s 
    INNER JOIN EmployeeStores AS es ON s.StoreId=es.StoreId) 
    INNER JOIN Employees AS e ON es.EmployeeId=e.EmployeeID) 
    INNER JOIN Titles AS t ON e.TitleId=t.TitleId WHERE t.IsManager=True AND e.IsActive=True)  AS d;

When I run it I still get inactive employees. What am I doing wrong?

[NTC]

not sure: looked at code but nothing jumped out at me.

but consider this. Since your result is to be inactives only. Begin with inactives only. This means rather than begin your Select statement of the table - instead make & save a simple select query of Inactives only...let's call this InactiveQ.

then begin your Select using the InactiveQ object.

the query may run faster in eliminating those AND as a by product too....

might work.