Select where date is incremented

[Mbowe]

Hi, I would like to select members who joined the club from 1956 to 2006, whom have been renewing their membership every year (for 50 years continuously. Any help?

select *
from tblMembers
where join_date='#01/01/1956#' and paid_tru='#12/31/2006#'

Hint: Members are renewing their membership by paying every year (paid_thru). I want to select the ones whom they have never skipped paying a yearly fee.

 

[TamarGranor]

How do you track payment of yearly fees? Is tblMembers the only table you have, or is there a table of payments?

Tamar

 

[Mbowe]

Everything is in one table, I mean joined_date, paid_thru, names, etc.

 

[alvechurchdata]

Everything is in one table

Do you then have 50 fields named "Paid1956", "Paid1957", "Paid1958" through to "Paid2006"?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[albertdc]

how about giving this a try

SELECT * ;
    FROM tblMembers
    WHERE YEAR(join_date) = 1956 ;
      AND YEAR(paid_tru) = 1957 ;
      AND YEAR(paid_tru) = 1958 ;
      ... so on ...
      AND YEAR(paid_tru) = 2006

tip: you may create the [ YEAR(paid_tru) = nnnn ] conditions dynamically by putting the year values in an array and creating the conditions via a loop.

note: i haven't tested the code, but, i think it should work. just give it a go.

good luck!

Albert Cobacha

 

[JimWWinter]

That would only work if there are separate records for each year. Typically, a simple system like that would have just one record for member that could be used to determine a persons membership status at the current time.

Regards,
Jim

 

[Mbowe]

albertdc, I thought of using

AND YEAR(paid_tru) = 1957 ;
AND YEAR(paid_tru) = 1958 ;
... so on ...

then I find it that it's going to be very long. I am not sure of using the array, I will try if I can.

JimWWinter, that's what I am looking now, something used to determine a persons membership status at the current time. I am new to the company and no doc about the database (explanation of the fields). I tried to use:

WHERE join_date<='#01/01/1956#' AND count(paid_thru)>=50

did select any records, I am not sure if count works well with date

 

[JimWWinter]

So, is paid_thru a date field? Do members renew their membership monthly, quarterly, annually, something else? Do you have a separate record for each renewal?

Oh, and if paid_thru is a date field that WHERE clause isn't going to work.

Regards,
Jim

 

[TamarGranor]

MBowe -- do you have one record per member or one record per member per yar?

If it's one per member, then your original query should work. If there's a new record for each member each year, try something like this:

SELECT Name, Count(*) AS nYears ;
  FROM tblMembers ;
  WHERE join_date >= DATE(1956,1,1) ;
  GROUP BY Name ;
  HAVING nYears >= 50
  INTO CURSOR FiftyYearMembers

Tamar

 

[Mbowe]

JimWWinter, paid_thru is a date field.

TamarGranor, I will have to double check about your quiz and try your code.

Thanks.