Years employed

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


--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Daryl" wrote in message
news
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!

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

"Daryl" wrote in message
news
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!

Thanks for your response Allen.
I cannot seem to get past step 6 without getting a requst for parameter
value maxofeffdate.

"Allen Browne" wrote:

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


--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Daryl" wrote in message
news
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!

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."




"John Spencer" wrote:

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

"Daryl" wrote in message
news
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!

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


"Daryl" wrote in message
...
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."




"John Spencer" wrote:

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

"Daryl" wrote in message
news
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!

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

"John Spencer" wrote:

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


"Daryl" wrote in message
...
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."




"John Spencer" wrote:

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

"Daryl" wrote in message
news 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!

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

"Daryl" wrote in message
...
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

"John Spencer" wrote:

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


"Daryl" wrote in message
...
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."




"John Spencer" wrote:

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

"Daryl" wrote in message
news 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!

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Daryl" wrote in message
...
Thanks for your response Allen.
I cannot seem to get past step 6 without getting a requst for parameter
value maxofeffdate.

"Allen Browne" wrote:

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

"Daryl" wrote in message
news
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?

-----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-----


Daryl wrote:
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?