Counting Consecutive Years

[Jimmy G]

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.

 

[Michel Walsh]

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

 

[Jimmy G]

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 Walsh]

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

 

[Michel Walsh]

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

ORDER BY rank


to


ORDER BY COUNT(*)




Vanderghast, Access MVP