Retirement Years Query Calculation

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

Guest

Trying to obtain "Years to Retire" with a query taking into consideration the
following criteria:

Criteria: If Age is >=55 and 30 years of service then 0 years remaining to
retire OR if Age >=60 and have 2 years of service then 0 years to retire,
otherwise how many years remaining to retire until one of these criterias is
met?

Presently have the following columns of information below: (as of 2007)
Years of Service Age Years to Retire
33 55 = 0
4 62 = 0
16 45 = ?
20 48 = ?

Please Help!

-
Carly(access fan)
 
Hi -

Test this query against Northwind's Employees table. Think it'll be pretty
close. [Emp] returns current years of service while [Ret] returns the
earliest retirement age.

SELECT
Employees.LastName
, Employees.FirstName
, Employees.BirthDate
, Employees.HireDate
, DateDiff("yyyy",[BirthDate],Date())+(DateSerial(Year(Date()),Month(
[BirthDate]),Day([BirthDate]))>Date()) AS Age
, DateDiff("yyyy",[HireDate],Date())+(DateSerial(Year(Date()),Month(
[HireDate]),Day([HireDate]))>Date()) AS Emp
, IIf([emp]+60-[age]>=30,[age]+30-[emp],IIf([emp]>=2,60,60+2-[emp])) AS Ret
FROM
Employees;

HTH - Bob
 
Hey Thanks for helping out, I really appreciate it. So close, however, I
must report on remaingin years until retirement. How can have - replaced to
show 0. The SQL added was as follows ([Ret]-[Age]) AS Remaining. Any ideas?

This is the result

LastName BirthDate PenDate Age PenYears Ret Remaining
Angela 08/05/1958 06/01/1992 49 15 60 11
Richard 25/06/1965 01/03/2001 42 6 60 18
Shams 30/10/1945 28/01/2002 62 5 60 -2
Shameela 08/04/1970 25/04/2002 37 5 60 23
Simon 24/05/1951 20/01/1977 56 30 56 0
Joulekhan 06/05/194620/10/1997 61 10 60 -1

--
Carly(access fan)


raskew via AccessMonster.com said:
Hi -

Test this query against Northwind's Employees table. Think it'll be pretty
close. [Emp] returns current years of service while [Ret] returns the
earliest retirement age.

SELECT
Employees.LastName
, Employees.FirstName
, Employees.BirthDate
, Employees.HireDate
, DateDiff("yyyy",[BirthDate],Date())+(DateSerial(Year(Date()),Month(
[BirthDate]),Day([BirthDate]))>Date()) AS Age
, DateDiff("yyyy",[HireDate],Date())+(DateSerial(Year(Date()),Month(
[HireDate]),Day([HireDate]))>Date()) AS Emp
, IIf([emp]+60-[age]>=30,[age]+30-[emp],IIf([emp]>=2,60,60+2-[emp])) AS Ret
FROM
Employees;

HTH - Bob

Carly said:
Trying to obtain "Years to Retire" with a query taking into consideration the
following criteria:

Criteria: If Age is >=55 and 30 years of service then 0 years remaining to
retire OR if Age >=60 and have 2 years of service then 0 years to retire,
otherwise how many years remaining to retire until one of these criterias is
met?

Presently have the following columns of information below: (as of 2007)
Years of Service Age Years to Retire
33 55 = 0
4 62 = 0
16 45 = ?
20 48 = ?

Please Help!

-
Carly(access fan)
Click to expand...
Click to expand...
 
Carly -
Returning to the Employees table, change the query to:

SELECT
LastName
, FirstName
, BirthDate
, HireDate
, DateDiff("yyyy",[BirthDate],Date())+(DateSerial(Year(Date()),Month(
[BirthDate]),Day([BirthDate]))>Date()) AS Age
, DateDiff("yyyy",[HireDate],Date())+(DateSerial(Year(Date()),Month(
[HireDate]),Day([HireDate]))>Date()) AS Emp
, IIf([emp]+60-[age]>=30,[age]+30-[emp],IIf([emp]>=2,60,60+2-[emp])) AS Ret
, Format(IIf([ret]=60,DateAdd("yyyy",60,[birthdate]),DateAdd("yyyy",30,
[hiredate])),"Medium Date") AS RetDte
FROM
Employees;

This will return the earliest retirement date [RetDte]. From there you can
add whatever refinements are necessary, including some logic if the employee
hits age 60 but has less than 2 years employment.

Bob

Carly said:
Hey Thanks for helping out, I really appreciate it. So close, however, I
must report on remaingin years until retirement. How can have - replaced to
show 0. The SQL added was as follows ([Ret]-[Age]) AS Remaining. Any ideas?

This is the result

LastName BirthDate PenDate Age PenYears Ret Remaining
Angela 08/05/1958 06/01/1992 49 15 60 11
Richard 25/06/1965 01/03/2001 42 6 60 18
Shams 30/10/1945 28/01/2002 62 5 60 -2
Shameela 08/04/1970 25/04/2002 37 5 60 23
Simon 24/05/1951 20/01/1977 56 30 56 0
Joulekhan 06/05/194620/10/1997 61 10 60 -1
Hi -
Click to expand...
[quoted text clipped - 36 lines]
Click to expand...
 
Hi Bob,

It's impossible, as per the new sql she can only retire when she is 67 yrs
old.

She can retire when she is 55 and 30 yrs service.

The criterias are: (retire when you have the following):
either
55 yrs old and 30 yrs service, which is the (85 Factor) Or
60 yrs old and 2 yrs service

Ex: Pratte will be 67 when she can retire, it is impossible, because she is
eligible to retire in 2017 because she will be 55 years old and have 30 years
service.

Its complicated

Results
LastName BirthDate HireDate Age Emp Ret RetDte
Abbassi, 25/04/197230/06/1999 35 8 57 June 30, 2029
Abi Ramya/12/195115/07/1990 5517 60 December 16, 2011
Adamidis 24/06/196708/06/1999 40 8 60 June 24, 2027
Agard 08/11/195311/03/199454 13 60 November 8, 2013
Ahlfeld 11/05/195327/11/200054 6 60 May 11, 2013
Ahmed 26/08/196920/10/199738 10 58 October 20, 2027
Ahti 20/05/195104/09/198856 19 60 May 20, 2011
Alabre 24/07/194712/05/198260 25 60 July 24, 2007
Pratte 14/01/196225/02/199745 10 60 January 14, 2022

Thanks

Carlys

--
Carly(access fan)


raskew via AccessMonster.com said:
Carly -
Returning to the Employees table, change the query to:

SELECT
LastName
, FirstName
, BirthDate
, HireDate
, DateDiff("yyyy",[BirthDate],Date())+(DateSerial(Year(Date()),Month(
[BirthDate]),Day([BirthDate]))>Date()) AS Age
, DateDiff("yyyy",[HireDate],Date())+(DateSerial(Year(Date()),Month(
[HireDate]),Day([HireDate]))>Date()) AS Emp
, IIf([emp]+60-[age]>=30,[age]+30-[emp],IIf([emp]>=2,60,60+2-[emp])) AS Ret
, Format(IIf([ret]=60,DateAdd("yyyy",60,[birthdate]),DateAdd("yyyy",30,
[hiredate])),"Medium Date") AS RetDte
FROM
Employees;

This will return the earliest retirement date [RetDte]. From there you can
add whatever refinements are necessary, including some logic if the employee
hits age 60 but has less than 2 years employment.

Bob

Carly said:
Hey Thanks for helping out, I really appreciate it. So close, however, I
must report on remaingin years until retirement. How can have - replaced to
show 0. The SQL added was as follows ([Ret]-[Age]) AS Remaining. Any ideas?

This is the result

LastName BirthDate PenDate Age PenYears Ret Remaining
Angela 08/05/1958 06/01/1992 49 15 60 11
Richard 25/06/1965 01/03/2001 42 6 60 18
Shams 30/10/1945 28/01/2002 62 5 60 -2
Shameela 08/04/1970 25/04/2002 37 5 60 23
Simon 24/05/1951 20/01/1977 56 30 56 0
Joulekhan 06/05/194620/10/1997 61 10 60 -1
Hi -
Click to expand...
[quoted text clipped - 36 lines]
-
Carly(access fan)
Click to expand...
Click to expand...
Click to expand...
 
Carly -

Are you actually running the query?

Pratte is currently age 45 with 10 years of service.
At age 60 she'll have 25 (10 + 15) years of service, not age 55
with 30 years of service.

The query accurately reflects this.

Bob

Carly said:
Hi Bob,

It's impossible, as per the new sql she can only retire when she is 67 yrs
old.

She can retire when she is 55 and 30 yrs service.

The criterias are: (retire when you have the following):
either
55 yrs old and 30 yrs service, which is the (85 Factor) Or
60 yrs old and 2 yrs service

Ex: Pratte will be 67 when she can retire, it is impossible, because she is
eligible to retire in 2017 because she will be 55 years old and have 30 years
service.

Its complicated

Results
LastName BirthDate HireDate Age Emp Ret RetDte
Abbassi, 25/04/197230/06/1999 35 8 57 June 30, 2029
Abi Ramya/12/195115/07/1990 5517 60 December 16, 2011
Adamidis 24/06/196708/06/1999 40 8 60 June 24, 2027
Agard 08/11/195311/03/199454 13 60 November 8, 2013
Ahlfeld 11/05/195327/11/200054 6 60 May 11, 2013
Ahmed 26/08/196920/10/199738 10 58 October 20, 2027
Ahti 20/05/195104/09/198856 19 60 May 20, 2011
Alabre 24/07/194712/05/198260 25 60 July 24, 2007
Pratte 14/01/196225/02/199745 10 60 January 14, 2022

Thanks

Carlys
Carly -
Returning to the Employees table, change the query to:
Click to expand...
[quoted text clipped - 39 lines]
Click to expand...
 
Carly,

Try to envision the problem as follows, then proceed with developing
solutions. Consider the Switch() function (see Help File).

Based on the specified criteria, you potentially have 5 possibilities:

(1) Employee will reach 30 or more years of service prior to 55th birthday.
(2) Employee is currently between age 55 and 59 and will have 30 or more
years of service prior to reaching age 60..
(3) Employee is < 60 years of age and will have 2 - 29 years of service upon
reaching age 60.
(4) Employee’s current age is >=60 years and has >=2 years of service
(5) Employee’s age is >=60 years and has <2 years of service

Solutions:

(1) Employee is eligible for retirement upon reaching age 55
(2) Employee is eligible for retirement upon reaching 30 years of service.
(3) Employee is eligible for retirement upon reaching age 60.
(4) Employee is immediately eligible for retirement.
(5) Employee is eligible for retirement upon reaching 2 years of service.

Bob
Carly -

Are you actually running the query?

Pratte is currently age 45 with 10 years of service.
At age 60 she'll have 25 (10 + 15) years of service, not age 55
with 30 years of service.

The query accurately reflects this.

Bob
Hi Bob,
Click to expand...
[quoted text clipped - 35 lines]
Click to expand...
 
Back
Top