A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Very difficult query!



 
 
Thread Tools Display Modes
  #1  
Old March 2nd, 2010, 02:59 PM posted to microsoft.public.access.queries
PayeDoc
external usenet poster
 
Posts: 103
Default 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  
Old March 2nd, 2010, 03:21 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default 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  
Old March 2nd, 2010, 04:16 PM posted to microsoft.public.access.queries
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default 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  
Old March 3rd, 2010, 11:17 AM posted to microsoft.public.access.queries
PayeDoc
external usenet poster
 
Posts: 103
Default 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  
Old March 3rd, 2010, 11:23 AM posted to microsoft.public.access.queries
PayeDoc
external usenet poster
 
Posts: 103
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.