Access Query to Calculate Turnover?

[jaswmil]

I have a query (see SQL below) that essentially grabs a list of all
employees terminated during a specific period. What I am needing to do
is to be able to take this number and then divide it by the CURRENT
count of active employees. The problem that I cannot figure out, is
how do I do this by department. Of course all of our departments want
to be able to see their own specific turnover rate. I can figure out
how to do this on a global level, but I can't on a departmental level.
As some background, I am pulling data from PS89 and using MS Access to
manipulate the data.

Please keep in mind that I am rather new to writing queries.

THANKS!

SELECT PUBLIC_PS_UM_EMPLOYEES.EMPLID, PUBLIC_PS_UM_EMPLOYEES.NAME,
PUBLIC_PS_UM_EMPLOYEES.EFFDT_JOB AS EFFECTIVE,
PUBLIC_PS_UM_EMPLOYEES.ACTION, PUBLIC_PS_UM_EMPLOYEES.ACTION_REASON,
PUBLIC_PS_UM_EMPLOYEES.ACTION_RSN_DESCR, PUBLIC_PS_UM_EMPLOYEES.DEPTID,
PUBLIC_PS_UM_EMPLOYEES.DEPTNAME, PUBLIC_PS_UM_EMPLOYEES.JOBCODE,
Departments.Manager, Departments.[Associate Director or Director],
Departments.[Hospital or Corporate Director], Departments.Facility,
PUBLIC_PS_UM_EMPLOYEES.EFFDT_JOB, PUBLIC_PS_UM_EMPLOYEES.JOBTITLE,
PUBLIC_PS_UM_EMPLOYEES.FTE, [EFFDT_JOB]-[HIRE_DT] AS [DAYS EMPLOYED]
FROM PUBLIC_PS_UM_EMPLOYEES LEFT JOIN Departments ON
PUBLIC_PS_UM_EMPLOYEES.DEPTID = Departments.[Department ID]
WHERE (((PUBLIC_PS_UM_EMPLOYEES.EFFDT_JOB) Between [From when?] And [To
when?]) AND ((PUBLIC_PS_UM_EMPLOYEES.ACTION)="TER" Or
(PUBLIC_PS_UM_EMPLOYEES.ACTION)="RET") AND
((PUBLIC_PS_UM_EMPLOYEES.ACTION_RSN_DESCR) Not Like "End of
Appointment") AND ((PUBLIC_PS_UM_EMPLOYEES.JOBCODE) Not In
("9220","4693","4960","5889","6427","6428","6436", "7761","7762","7763","7764","7765","7766","7767"," 7768","7769","7771","7772","7776","8394","8398","8 711","8715","8881","8883","8884","8885"))
AND ((PUBLIC_PS_UM_EMPLOYEES.BUSINESS_UNIT)="HOSPT"));

[Jeff L]

Change/copy your query so that you are only selecting DeptID, DeptName,
DeptID. Click View, Totals. In the totals row of the query, put Group
By in the first two and Count in the last column. This query will give
you a list of departments and the number of employees that have been
terminated.

Now make a similar query only this time make it so it grabs the current
employees. Now you have a list of departments and the number of
current employees.

Make a third query, using the first two and joining on DeptID. Output
DeptID, Dept, [TotalFromQuery1]/[TotalFromQuery2].

Hope that helps!
ja*****@gmail.com wrote:

I have a query (see SQL below) that essentially grabs a list of all
employees terminated during a specific period. What I am needing to do
is to be able to take this number and then divide it by the CURRENT
count of active employees. The problem that I cannot figure out, is
how do I do this by department. Of course all of our departments want
to be able to see their own specific turnover rate. I can figure out
how to do this on a global level, but I can't on a departmental level.
As some background, I am pulling data from PS89 and using MS Access to
manipulate the data.

Please keep in mind that I am rather new to writing queries.

THANKS!

SELECT PUBLIC_PS_UM_EMPLOYEES.EMPLID, PUBLIC_PS_UM_EMPLOYEES.NAME,
PUBLIC_PS_UM_EMPLOYEES.EFFDT_JOB AS EFFECTIVE,
PUBLIC_PS_UM_EMPLOYEES.ACTION, PUBLIC_PS_UM_EMPLOYEES.ACTION_REASON,
PUBLIC_PS_UM_EMPLOYEES.ACTION_RSN_DESCR, PUBLIC_PS_UM_EMPLOYEES.DEPTID,
PUBLIC_PS_UM_EMPLOYEES.DEPTNAME, PUBLIC_PS_UM_EMPLOYEES.JOBCODE,
Departments.Manager, Departments.[Associate Director or Director],
Departments.[Hospital or Corporate Director], Departments.Facility,
PUBLIC_PS_UM_EMPLOYEES.EFFDT_JOB, PUBLIC_PS_UM_EMPLOYEES.JOBTITLE,
PUBLIC_PS_UM_EMPLOYEES.FTE, [EFFDT_JOB]-[HIRE_DT] AS [DAYS EMPLOYED]
FROM PUBLIC_PS_UM_EMPLOYEES LEFT JOIN Departments ON
PUBLIC_PS_UM_EMPLOYEES.DEPTID = Departments.[Department ID]
WHERE (((PUBLIC_PS_UM_EMPLOYEES.EFFDT_JOB) Between [From when?] And [To
when?]) AND ((PUBLIC_PS_UM_EMPLOYEES.ACTION)="TER" Or
(PUBLIC_PS_UM_EMPLOYEES.ACTION)="RET") AND
((PUBLIC_PS_UM_EMPLOYEES.ACTION_RSN_DESCR) Not Like "End of
Appointment") AND ((PUBLIC_PS_UM_EMPLOYEES.JOBCODE) Not In
("9220","4693","4960","5889","6427","6428","6436", "7761","7762","7763","7764","7765","7766","7767"," 7768","7769","7771","7772","7776","8394","8398","8 711","8715","8881","8883","8884","8885"))
AND ((PUBLIC_PS_UM_EMPLOYEES.BUSINESS_UNIT)="HOSPT"));