Tags: access, allemployees, below, calculate, database, dois, essentially, grabs, microsoft, mysql, needing, oracle, period, query, specific, sql, terminated, turnover

Access Query to Calculate Turnover?

On Database » Microsoft Access

4,972 words with 1 Comments; publish: Thu, 03 Jan 2008 11:08:00 GMT; (25061.52, « »)

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","8711","8715","8881","8883","8884","8885"))

AND ((PUBLIC_PS_UM_EMPLOYEES.BUSINESS_UNIT)="HOSPT"));

All Comments

Leave a comment...

  • 1 Comments
    • 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!

      jaswmil.ms-access.todaysummary.com.gmail.com wrote:

      Quote:
      === Original Words ===

      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","8711","8715","8881","8883","8884","8885"))

      AND ((PUBLIC_PS_UM_EMPLOYEES.BUSINESS_UNIT)="HOSPT"));

      #1; Thu, 03 Jan 2008 11:10:00 GMT