I have these tables:
http://www.maroisconsulting.com/temp/query.png
An employee has a title, and optionally a title could represent a manager positon. An employee can work at one or more stores, and each store in in only one corporate group.
I need to get back a result set that shows the percentage of employees who are managers who have a date in the Certified field. It needs to be grouped by Group.
Right now there are 4 groups, so my result set should look like:
Code:
Group A 11
Group B 32
Group C 28
Group D 15
Here's what I have so far, but it doesn't account for the IsManager field in the Titles table, and I can't seem to get the Titles table int the query correctly.
Code:
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 INNER JOIN Groups ON
d.GroupId=Groups.GroupId
GROUP BY d.GroupId;
and
Code:
SELECT q.GroupId, (SumOfcert_complete/CountOfcert_complete)*100 AS
percent_certified, Groups.GroupName
FROM qryGroupCert_base AS q INNER JOIN Groups ON
q.GroupId=Groups.GroupId;
Like I said, these 2 queries don't account for only those employees who are managers (Titles.IsManager), so the result set it wrong.
I could use a bit of help on this one.
Thanks