 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2007Retirement Years Query Calculation
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 >Trying to obtain "Years to Retire" with a query taking into consideration the >following criteria: [quoted text clipped - 15 lines] >- >Carly(access fan) 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  SignatureCarly(access fan) > Hi - > [quoted text clipped - 36 lines] > >- > >Carly(access fan) 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 >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 [quoted text clipped - 15 lines] >> >- >> >Carly(access fan) 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 SignatureCarly(access fan) > Carly - > Returning to the Employees table, change the query to: [quoted text clipped - 39 lines] > >> >- > >> >Carly(access fan) 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, > [quoted text clipped - 35 lines] >> >> >- >> >> >Carly(access fan) 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 - > [quoted text clipped - 13 lines] >>> >> >- >>> >> >Carly(access fan) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.