Years employed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I calculate years employed for the following:

Table1
emplid(PK),effdate,action(Hire,Termination,Rehire)

100,1/23/04,HIR
100,8/06/04,TER
100,10/11/05,REH
100,01/24/06,TER
111,1/12/00,HIR
111,5/06/01,TER
151,8/12/00,HIR
151,11/23/04,TER
151,1/23/05,REH

I know I need a subquery somehow...but what other functions should I use?

Thanks for your help!
 
1. Create a query using your table.

2. Depress the Total button on the toolbar.
Access adds a Total row to the grid.

3. Drag EmpID into the grid.
Accept Group By in the Total row.

4. Drag Action into the grid.
In the Total row under this field, choose Where.
In the Criteria row under this field, enter:
"HIR" or "REH"
so it restricts the query to only hiring dates.

5. Drag the EffDate field into the grid.
In the Total row, choose Max.

The query now shows 1 row for each employee, with their most recent hire or
rehire date.

6. Type this expression into a fresh column in the Field row, and choose
Expression in the Group By row:

(SELECT Min(EffDate) FROM Table1 AS Dupe
WHERE (Dupe.Action = "TER")
AND (Dupe.EffDate > MaxOfEffDate)
AND (Dupe.EmpID = Table1.EmpID))

The new column should give you the first termination date for the person
after their most recent hire/rehire date. If it won't play ball, save the
query at step 5. Then create a new query using the other one as an input
"table", and try the expression again.

7. Once you have that working, you can calculate the difference in years.
Presumably you want to use today's date if the employeement is still
current, so that is:

Nz((SELECT Min(EffDate) FROM Table1 AS Dupe
WHERE (Dupe.Action = "TER")
AND (Dupe.EffDate > MaxOfEffDate)
AND (Dupe.EmpID = Table1.EmpID)),Date())

Now for the difference in years, use something like:
Age(MaxOfEffDate,
Nz((SELECT Min(EffDate) FROM Table1 AS Dupe
WHERE (Dupe.Action = "TER")
AND (Dupe.EffDate > MaxOfEffDate)
AND (Dupe.EmpID = Table1.EmpID)),Date()))

grabbing the Age() function from:
http://allenbrowne.com/func-08.html
 
Assumptions:
Use today's date to calculate end of employment period if there is no
relevant termination
Total employment time is the sum of employment periods (hir to ter plus reh
to ter plus reh to ter)
Days / 365.25 is a good enough estimate.

UNTESTED SQL AIRCODE follows

SELECT EmplID
, Int(Sum(DateDiff("d",T.EffDate,
NZ((SELECT Min(EffDate)
FROM Table1 as T
WHERE T.EffDate > M.EffDate
AND Action = "Ter") ,Date()))) / 365.25) as Years
FROM Table1 as M
WHERE Action in ("HIR","REH")
GROUP BY EmpID
 
Thanks for your response Allen.
I cannot seem to get past step 6 without getting a requst for parameter
value maxofeffdate.
 
Thanks John, I appreciate your reply.

I receive an error stating
"You tried to execute a query that does not include the specified expression
'EFFDATE' as part of an aggregate function."
 
Well there was an error in my code. I referred to T.EffDate when I Should
have referred to M.Effdate. That may have caused the error message. Try
the following and if it doesn't work, then post the SQL you are using.

SELECT EmplID
, Int(Sum(DateDiff("d",M.EffDate,
NZ((SELECT Min(T.EffDate)
FROM Table1 as T
WHERE T.EffDate > M.EffDate
AND Action = "Ter") ,Date()))) / 365.25) as Years
FROM Table1 as M
WHERE Action in ("HIR","REH")
GROUP BY EmpID
 
I get the same error. Here is what I'm using:

SELECT EMPLID, Int(Sum(DateDiff("d",M.EFFDT,
NZ((SELECT Min(T.EFFDT)
FROM dbo_dp_law_info_vw5 as T
WHERE T.EFFDT > M.EFFDT
AND Action = "Ter") ,Date()))) / 365.25) as Years
FROM dbo_dp_law_info_vw5 as M
WHERE Action in ("HIR","REH")
GROUP BY EMPLID
 
Interesting, I wouldn't think you would need to group by it, by try adding
the EFFDT to the GROUP BY CLAUSE

SELECT EMPLID, Int(Sum(DateDiff("d",M.EFFDT,
NZ((SELECT Min(T.EFFDT)
FROM dbo_dp_law_info_vw5 as T
WHERE T.EFFDT > M.EFFDT
AND Action = "Ter") ,Date()))) / 365.25) as Years
FROM dbo_dp_law_info_vw5 as M
WHERE Action in ("HIR","REH")
GROUP BY EMPLID, EFFDT
 
Did you try saving the query after step 5?
Then use that query as a source "table" to go on with in the next query.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The easiest way to solve this is to have a table designed like this:

emplid, hire_date, termination_date

When the end_date is NULL then the employee is still employed.

If you wish to continue using your current table design your solution
would be via a query like this:

SELECT T.emplid,
(SELECT Min(effdate) FROM table1
WHERE emplid = T.emplid AND action = 'TER'
AND effdate >= T.effdate) - T.effdate AS DaysEmployed
FROM table1 As T
WHERE T.action IN ('HIR','REH')
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ+5CJYechKqOuFEgEQJHAQCeL/ZASHGHruteSwrD5F7Z9KcV45kAoNfo
G2/SxfEgEGzkSdpSuMr2qn2O
=kkGv
-----END PGP SIGNATURE-----
 
Back
Top