It works but the query that calls it does not see it because the GroupId is 0 and there's no GroupId to join on. I could add a Group to the Groups table called "All", but then I'd have to hide it in other places.

What do you think?


>>[UPDATED]
>>
>>First, I just realized this is Access, not SQL Server. I customer sent it to me
>>
>>Second, I left out qryGroupCert_Base in my OP:
>>
>>

>>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)  AS d
>>    GROUP BY d.GroupId;
>>

>>
>>I think that this is probably the right place to do this.
>>
>>Sorry for the confusion.
>
>Well, according to this link http://www.msofficesolved.com/general/Q_25565745-Rollup-and-Cube-in-Microsoft-Access.jsp Access does not have CUBE or ROLLUP, which means we need to use UNION ALL solution, e.g.
>

>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)  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)  AS d
>

>
>I'm not sure if this will work in Access, but worth a quick try.