CODEWITHSUNDEEP
ms-accessms-access-2007
CoderForHire
CoderForHire

Reputation: 439

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

EmployeeId 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?

Upvotes: 0

Views: 73

Answers (1)

HansUp
HansUp

Reputation: 97101

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;

Upvotes: 1

Related Questions