Very difficult query!

  • Thread starter Thread starter PayeDoc
  • Start date Start date
P

PayeDoc

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
 
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
 
On Tue, 2 Mar 2010 13:59:01 -0000, "PayeDoc"

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
 
Tom

Brilliant - that worked a treat!
Wish I'd thought of that - perhaps next time!

Many thanks
Les
 
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
 
Back
Top