If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Very difficult query!
Hello All
I have spent most of today on this and can't get it right: I hope someone out there can help! I need a query that will give a person's retirement date (RD), according to their date of birth (DOB). The trouble is that the age at which a person can retire is not constant, but is instead determined according to the following: for a DOB earlier than 6 April 1950, the RD is on their 60th birthday If DOB is 6 April 1950 to 5 May 1950, RD is 6 May 2010 If DOB is 6 May 1950 to 5 June1950, RD is 6 July 2010 If DOB is 6 June 1950 to 5 July 1950, RD is 6 Sep 2010 If DOB is 6 July 1950 to 5 Aug 1950, RD is 6 Nov 2010 If DOB is 6 Aug 1950 to 5 Sep 1950, RD is 6 Jan 2011 If DOB is 6 Sep 1950 to 5 Oct 1950, RD is 6 Mar 2011 etc. etc. until If DOB is 6 Mar 1952 to 5 Apr 1952, RD is 6 Mar 2013 If DOB is 6 Apr 1952 to 5 May 1952, RD is 6 May 2013 for a DOB later than 5 May 1952, the RD is on their 61st birthday That's it - and believe it or not, this is real: the UK government has decided to 'phase in' the introduction of womens' retirement age becoming 61! Many thanks for any help. Leslie Isaacs |
#2
|
|||
|
|||
Very difficult query!
Have a table for this data, like:
Retirements 'table FromD ToD Retire Age 'fields 1900.1.1 1950.4.6 null 60 1950.4.6 1950.5.6 2010.5.6 null .... 1952.4.6 1952.5.6 2013.5.6 null 1952.5.6 3000.1.1 null 61 'data And have your SQL statement like: SELECT *, NZ(retirements.retire, DateAdd("yyyy", retirements.age, mytable.dob )) FROM mytable INNER JOIN retirements ON mytable.dob = retirements.fromD AND mytable.dob retirement.toD Vanderghast, Access MVP "PayeDoc" wrote in message ... Hello All I have spent most of today on this and can't get it right: I hope someone out there can help! I need a query that will give a person's retirement date (RD), according to their date of birth (DOB). The trouble is that the age at which a person can retire is not constant, but is instead determined according to the following: for a DOB earlier than 6 April 1950, the RD is on their 60th birthday If DOB is 6 April 1950 to 5 May 1950, RD is 6 May 2010 If DOB is 6 May 1950 to 5 June1950, RD is 6 July 2010 If DOB is 6 June 1950 to 5 July 1950, RD is 6 Sep 2010 If DOB is 6 July 1950 to 5 Aug 1950, RD is 6 Nov 2010 If DOB is 6 Aug 1950 to 5 Sep 1950, RD is 6 Jan 2011 If DOB is 6 Sep 1950 to 5 Oct 1950, RD is 6 Mar 2011 etc. etc. until If DOB is 6 Mar 1952 to 5 Apr 1952, RD is 6 Mar 2013 If DOB is 6 Apr 1952 to 5 May 1952, RD is 6 May 2013 for a DOB later than 5 May 1952, the RD is on their 61st birthday That's it - and believe it or not, this is real: the UK government has decided to 'phase in' the introduction of womens' retirement age becoming 61! Many thanks for any help. Leslie Isaacs |
#3
|
|||
|
|||
Very difficult query!
On Tue, 2 Mar 2010 13:59:01 -0000, "PayeDoc"
wrote: I would use some VBA. The query is then simple: select PersonID, GetRD(DOB) from myTable The function GetRD must be in a standard module, and must be public: Public Function GetRD(ByVal dob As Date) As Date Dim rd As Date Select Case dob Case Is #5/5/1950# rd = DateAdd("y", 61, dob) Case Is #4/5/1952# rd = #5/6/2011# Case Is #3/5/1952# rd = #3/6/2011# 'etc. End Select GetRD = rd End Function The beauty of the Select Case statement is that VBA will execute the FIRST matching clause. That's why I have the date checks from youngest to oldest. -Tom. Microsoft Access MVP Hello All I have spent most of today on this and can't get it right: I hope someone out there can help! I need a query that will give a person's retirement date (RD), according to their date of birth (DOB). The trouble is that the age at which a person can retire is not constant, but is instead determined according to the following: for a DOB earlier than 6 April 1950, the RD is on their 60th birthday If DOB is 6 April 1950 to 5 May 1950, RD is 6 May 2010 If DOB is 6 May 1950 to 5 June1950, RD is 6 July 2010 If DOB is 6 June 1950 to 5 July 1950, RD is 6 Sep 2010 If DOB is 6 July 1950 to 5 Aug 1950, RD is 6 Nov 2010 If DOB is 6 Aug 1950 to 5 Sep 1950, RD is 6 Jan 2011 If DOB is 6 Sep 1950 to 5 Oct 1950, RD is 6 Mar 2011 etc. etc. until If DOB is 6 Mar 1952 to 5 Apr 1952, RD is 6 Mar 2013 If DOB is 6 Apr 1952 to 5 May 1952, RD is 6 May 2013 for a DOB later than 5 May 1952, the RD is on their 61st birthday That's it - and believe it or not, this is real: the UK government has decided to 'phase in' the introduction of womens' retirement age becoming 61! Many thanks for any help. Leslie Isaacs |
#4
|
|||
|
|||
Very difficult query!
Tom
Brilliant - that worked a treat! Wish I'd thought of that - perhaps next time! Many thanks Les "Tom van Stiphout" wrote in message ... On Tue, 2 Mar 2010 13:59:01 -0000, "PayeDoc" wrote: I would use some VBA. The query is then simple: select PersonID, GetRD(DOB) from myTable The function GetRD must be in a standard module, and must be public: Public Function GetRD(ByVal dob As Date) As Date Dim rd As Date Select Case dob Case Is #5/5/1950# rd = DateAdd("y", 61, dob) Case Is #4/5/1952# rd = #5/6/2011# Case Is #3/5/1952# rd = #3/6/2011# 'etc. End Select GetRD = rd End Function The beauty of the Select Case statement is that VBA will execute the FIRST matching clause. That's why I have the date checks from youngest to oldest. -Tom. Microsoft Access MVP Hello All I have spent most of today on this and can't get it right: I hope someone out there can help! I need a query that will give a person's retirement date (RD), according to their date of birth (DOB). The trouble is that the age at which a person can retire is not constant, but is instead determined according to the following: for a DOB earlier than 6 April 1950, the RD is on their 60th birthday If DOB is 6 April 1950 to 5 May 1950, RD is 6 May 2010 If DOB is 6 May 1950 to 5 June1950, RD is 6 July 2010 If DOB is 6 June 1950 to 5 July 1950, RD is 6 Sep 2010 If DOB is 6 July 1950 to 5 Aug 1950, RD is 6 Nov 2010 If DOB is 6 Aug 1950 to 5 Sep 1950, RD is 6 Jan 2011 If DOB is 6 Sep 1950 to 5 Oct 1950, RD is 6 Mar 2011 etc. etc. until If DOB is 6 Mar 1952 to 5 Apr 1952, RD is 6 Mar 2013 If DOB is 6 Apr 1952 to 5 May 1952, RD is 6 May 2013 for a DOB later than 5 May 1952, the RD is on their 61st birthday That's it - and believe it or not, this is real: the UK government has decided to 'phase in' the introduction of womens' retirement age becoming 61! Many thanks for any help. Leslie Isaacs |
#5
|
|||
|
|||
Very difficult query!
Vanderghast
Brilliant - I can see exactly how that would work! In fact I have used Tom's suggested function instead of the table, but I can see that both methods would do the trick. In other circumstances where it might be necessary to update the data periodically I can also see that it would be easier to maintain your table than the function (a general user could maintain the table), so I'll remember that! Thanks again Les "vanderghast" vanderghast@com wrote in message ... Have a table for this data, like: Retirements 'table FromD ToD Retire Age 'fields 1900.1.1 1950.4.6 null 60 1950.4.6 1950.5.6 2010.5.6 null ... 1952.4.6 1952.5.6 2013.5.6 null 1952.5.6 3000.1.1 null 61 'data And have your SQL statement like: SELECT *, NZ(retirements.retire, DateAdd("yyyy", retirements.age, mytable.dob )) FROM mytable INNER JOIN retirements ON mytable.dob = retirements.fromD AND mytable.dob retirement.toD Vanderghast, Access MVP "PayeDoc" wrote in message ... Hello All I have spent most of today on this and can't get it right: I hope someone out there can help! I need a query that will give a person's retirement date (RD), according to their date of birth (DOB). The trouble is that the age at which a person can retire is not constant, but is instead determined according to the following: for a DOB earlier than 6 April 1950, the RD is on their 60th birthday If DOB is 6 April 1950 to 5 May 1950, RD is 6 May 2010 If DOB is 6 May 1950 to 5 June1950, RD is 6 July 2010 If DOB is 6 June 1950 to 5 July 1950, RD is 6 Sep 2010 If DOB is 6 July 1950 to 5 Aug 1950, RD is 6 Nov 2010 If DOB is 6 Aug 1950 to 5 Sep 1950, RD is 6 Jan 2011 If DOB is 6 Sep 1950 to 5 Oct 1950, RD is 6 Mar 2011 etc. etc. until If DOB is 6 Mar 1952 to 5 Apr 1952, RD is 6 Mar 2013 If DOB is 6 Apr 1952 to 5 May 1952, RD is 6 May 2013 for a DOB later than 5 May 1952, the RD is on their 61st birthday That's it - and believe it or not, this is real: the UK government has decided to 'phase in' the introduction of womens' retirement age becoming 61! Many thanks for any help. Leslie Isaacs |
Thread Tools | |
Display Modes | |
|
|