UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Calculate Monthly Turnover Of Employees, Access 2016    
 
   
jrick
post Jun 11 2019, 04:31 AM
Post#1



Posts: 182
Joined: 22-January 03
From: South Carolina


I am asking a lot, but Desperate people do that.

I have a simple table. Employee, StartDate and TermDate
635 records
They are ordered by StartDate.

I need to know the average number of employees thay t were active each month and number of terminations each month to calculate Employee Turnover.

Have been trying to do with queries like this

Expr2: DCount("*","Tern",("[Hire Date] <= #" & [Hire Date] & "# AND [Term Date] IS NULL"))
Expr3: DCount("*","Tern",("[Hire Date] <= #" & [Hire Date] & "# AND [Term Date] >= #" & [Hire Date] & "#"))
These two added together gives me number of employees on that day. I couldnt figure out how to put in one expr and could not add the two fields in another expression

Step 2: Calculate average head count for each month
StepThree Count number of terminations for each month

Divide one by the other X 100 to get turnover rate.
Should I go back to queries or anyone willing to walk me through this, or Its obviously too advanced for me.

Thanks for the opportunity to ask.




Go to the top of the page
 
GroverParkGeorge
post Jun 11 2019, 08:04 AM
Post#2


UA Admin
Posts: 35,138
Joined: 20-June 02
From: Newcastle, WA


You should be able to do this in a single query, using Aggregate expressions.

It would be easier to put together an example if we had the table to work with, though. You offered two different versions, StartDate and HireDate. I assume the field is actually named "HireDate"?

This post has been edited by GroverParkGeorge: Jun 11 2019, 08:30 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
MadPiet
post Jun 11 2019, 10:02 AM
Post#3



Posts: 3,128
Joined: 27-February 09



I did it in SQL Server.

CODE
-- create and populate the Calendar table
CREATE TABLE Calendar (TheDate DATE PRIMARY KEY);
GO

DECLARE @SomeDate DATE = '01-Jan-2019';
WHILE @SomeDate<'31-DEC-2019'
BEGIN
    INSERT INTO Calendar(TheDate) VALUES (@SomeDate);
    SET @SomeDate = DATEADD(day,1,@SomeDate)
END;

CREATE TABLE Employee (
            EmployeeID INT IDENTITY,
            StartDate DATE NOT NULL,
            EndDate DATE );
GO            

-- populate the Employee table
INSERT INTO Employee (StartDate, EndDate)
VALUES ('12-31-2017','05-31-2019'),('03-01-2019','06-01-2019');


CODE
SELECT er.TheDate
    , SUM(er.IsEmployed)
FROM
(SELECT e.EmployeeID
    , e.StartDate
    , e.EndDate
    , c.TheDate
    , CASE WHEN c.TheDate >=e.StartDate AND c.TheDate <= e.EndDate THEN 1 ELSE 0 END AS IsEmployed
FROM Calendar c CROSS JOIN Employee e) er
GROUP BY er.TheDate;


so in Access it would be almost the same:

CODE
SELECT er.TheDate
    , SUM(er.IsEmployed)
FROM
(SELECT e.EmployeeID
    , e.StartDate
    , e.EndDate
    , c.TheDate
    , IIF( c.TheDate >=e.StartDate AND c.TheDate <= e.EndDate, 1,0) AS IsEmployed
FROM Calendar c CROSS JOIN Employee e) er
GROUP BY er.TheDate;


In a nutshell, you need a Calendar table with all the dates in the range you need to look at. The rest is easy.

Go to the top of the page
 
MadPiet
post Jun 11 2019, 12:44 PM
Post#4



Posts: 3,128
Joined: 27-February 09



Okay, pay not attention to that mess I posted earlier. I created a Calendar table (Year, Month, Day)

CODE
CREATE TABLE Calendar (TheDate DATE PRIMARY KEY, TheMonth TINYINT, TheYear INT);

DECLARE @SomeDate DATE = '01-Jan-2019';
WHILE @SomeDate<'31-DEC-2019'
BEGIN
    INSERT INTO Calendar(TheDate) VALUES (@SomeDate);
    SET @SomeDate = DATEADD(day,1,@SomeDate)
END;


-- populate the Year and Month columns
UPDATE Calendar SET TheYear = YEAR(TheDate), TheMonth = MONTH(TheDate);

-- return answer
CODE
SELECT c.TheYear
    ,  c.TheMonth
    , COUNT(DISTINCT(e.EmployeeID)) AS EmpCount
FROM Calendar c
    LEFT JOIN Employee e ON c.TheDate >= e.StartDate
        AND c.TheDate <= ISNULL(e.EndDate,'12-31-2019')
GROUP BY c.TheYear
    ,  c.TheMonth
ORDER BY c.TheYear
    ,  c.TheMonth;


I think in Access you have to do
CODE
SELECT x.TheYear
           , x.TheMonth
           , COUNT(x.EmployeeID) AS EmployeeCount
(
SELECT DISTINCT c.TheYear
    ,  c.TheMonth
    , e.EmployeeID
FROM Calendar c
    LEFT JOIN Employee e ON c.TheDate >= e.StartDate
        AND c.TheDate <= ISNULL(e.EndDate,'12-31-2019')
) x
GROUP BY x.TheYear, x.TheMonth
ORDER BY x.TheYear, x.TheMonth;
Go to the top of the page
 
jrick
post Jun 11 2019, 01:10 PM
Post#5



Posts: 182
Joined: 22-January 03
From: South Carolina


Thank you for your Kindness, Trouble is I am way over my head.
Table name is Tern
Fields are Employee, HireDate, and TermDate.

I first wanted to show how many employees were active on each HireDate.
Then Produce a table Named Turnover

first column would be each Month/Year Combo
Second Column would be average Active employees that month
Third Column would be count of TermDate that month
Fourth Column would be Third column divided by second column times 100
Which would be the turn over by month.

Not asking you to continue, just clarifying what I want.

Thank you again for the effort, I am not ready yet for what I tried to do.

I have about accomplished it by going all around the world.
The query with two expressions gave me the active. I could figure out how to combine them in one with a or statement
Then I tried to have a third expression to add them together. produced an error
So I made a table from the query where both expressions were numbers
Made another query to add them together
and now I have raw data.

Looking at making a crosstab to get average of that sum for each month and count of Term and done.

Thank you again.
Go to the top of the page
 
MadPiet
post Jun 11 2019, 01:15 PM
Post#6



Posts: 3,128
Joined: 27-February 09



first column would be each Month/Year Combo
Second Column would be average Active employees that month
Third Column would be count of TermDate that month
Fourth Column would be Third column divided by second column times 100
Which would be the turn over by month.

For the Avg Active Employees per Month - can you explain how to calculate that? You can get all the employees active in the month, and count them. Then what? divide by the number of days in the month?
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th June 2019 - 04:14 PM