Counting Consecutive Years

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to count the maximum number of consecutive years that an
individual has made a gift to our institution. I have a range of 35 years to
consider and all my information resides in one table. It doesn't matter when
the last giving year was as long as s/he give 'x' number of consecutive years
before that (with 'x' being a user-defined parameter). I will also want to
know the minimum and maximum values for that range. I'm using Access 2003 and
know very little Visual Basic. Any help would be appreciated.
 
If the fields are like:

Name YearContribution 'fields names
Joe Smith 1989
Joe Smith 1990
.... ' data sample



then, make a query:



SELECT a.name, a.yearContribution, COUNT(*) As rank
FROM myTable AS a INNER JOIN myTable AS b
ON a.name = b.name AND a.yearContribution >= b.yearContribution
GROUP BY a.name, a.yearContribution


which ranks the year of contribution, for each name. Use another technique
to rank, if you wish.

Let us call that query qu1. All un-interrupted sequences are next given by:


SELECT name, MIN(yearContribution) AS startingSequence, COUNT(*) AS
sequenceLength
FROM qu1
GROUP BY name, yearContribution - rank


Let call it qu2. That gives all starting sequences, and their 'length'
(un-interrupted). The trick is to consider the year as a ranking system, but
with 'potential' holes. If there is no hole, year - rank == constant, and
COUNT(*) just counts the number of such records (years) that are in the same
sequence (without hole).


The maximum sequence length is then given by:


SELECT name, MAX(sequenceLength)
FROM qu2
GROUP BY name



Calling that last query makes the other queries run automatically, as usual.




Hoping it may help,
Vanderghast, Access MVP
 
This works perfectly, but I'm confused about using rank. I'm prompted for the
parameter but if I click through, I get the desired result .
 
Michel,

In query 1, the 'rank' column is not displaying values in sequence. Is it
suppose to? For instance, for the same individual, the rank are:

ENTITYID YRGIVING Rank SC Amt
0000000016 1994 1 0.5
0000000016 1996 2 0.5
0000000016 1997 3 1.5
0000000016 1998 10 217.5
0000000016 2001 6 2400

I don't understand why the Rank values are 1,2,3,10, and 6 and not
1,2,3,4,5. Any help would be appreciated. Thanks.
 
Do you have just ONE record per { name, year} ? If a given name has many
records for the SAME year, that can generates wrong ranking (because of
ex-equo). Are you sure the rows with rank of 10 has the same entityID value
than form the rank of 3? It may be that one has O instead of 0, or some
extra ending spaces, or something like that, which will make the two rows
having DIFFERENT entityID (so on a different ranking sequence).


Vanderghast, Access MVP
 
You use it on an ORDER BY clause? If so, change

ORDER BY rank


to


ORDER BY COUNT(*)




Vanderghast, Access MVP
 
Back
Top