Get Percentages Of Rows With Date In Them - Grouped

I have the following tables with the relevant columns.

Each Employee can belong to one or more Stores. Each Store is in one Group.

Employees

Emp开发者_如何转开发loyeeId Number
Certified DateTime

EmployeeStores

EmployeeStoreId Number
EmployeeId Number
StoreId Number

Stores

StoreId Number
GroupId Number

I need to produce a query that displays the percentage of the Certified field completed in the Employees table by Group. So, assuming Group A, Group B, and Group C, the result set should have:

Group A 13
Group B 42
Group C 21
Other 24

How can this be done in Access?

Create this query, verify it returns correct values, and save it as qryGroupCert_base.

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

Then build a new query which uses qryGroupCert_base as its source.

SELECT
    q.GroupId,
    (SumOfcert_complete/CountOfcert_complete) * 100 AS percent_certified
FROM
    qryGroupCert_base AS q;