Problem Adding WHERE Clause

[zephaneas]

In my query shown below, I just want to add "WHERE e.IsActive = True" and when I do, I'm getting prompted for "e.IsActive".



What am I doing wrong??

 
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) 
    WHERE 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) 
    WHERE e.IsActive = True
 AS d;

[pbaldy]

If my eye is working correctly, "e" is defined within the subquery, but you're trying to use it in the outer query.