Trying to query long-term donors, but not necessarily consecutive years

Patti Micklin

We want to honor recent donors who've contributed for 5 or more years, but not consecutive years. What's the best way to do this? With the Consecutive canned report, it removes people who would fit our criteria. Thanks! Patti Micklin Director of Development Via Mobility Services

Melissa Graves 2

Patti Micklin:
We want to honor recent donors who've contributed for 5 or more years, but not consecutive years. What's the best way to do this? With the Consecutive canned report, it removes people who would fit our criteria. Thanks! Patti Micklin Director of Development Via Mobility Services

We do this for our longtime giving notation in our annual report. We do 20 years and my criteria is a) 20 consecutive years OR b) first gift 20+ years ago and number of gifts greater than or equal to 17.

I have heard that there are also ways to do this a) in SQL or using Pivot reports but I have not the time or patience to do either of these.

Jason Black

Patti Micklin:
We want to honor recent donors who've contributed for 5 or more years, but not consecutive years. What's the best way to do this? With the Consecutive canned report, it removes people who would fit our criteria. Thanks! Patti Micklin Director of Development Via Mobility Services

Patti,

I don't necessarily know the "best" way to do this, but here is a method I use for something similar.  I created a query that includes anyone who gave a gift during the current fiscal year.  For output I include a summary field for number of gifts for each fiscal year dating back as far as we have records (warning: this is a tedious pain to do the first time, but only requires one additional year forevermore).  I send that query to Excel, where I can quickly convert any number other than 0 in my summary fields to 1.  I then add a column in which to sum up the counts across all years to get total years given.

I hope that helps.  If you need further detail about any of these steps, let me know.

Melissa Graves 2

Jason Black:

Patti,

I don't necessarily know the "best" way to do this, but here is a method I use for something similar.  I created a query that includes anyone who gave a gift during the current fiscal year.  For output I include a summary field for number of gifts for each fiscal year dating back as far as we have records (warning: this is a tedious pain to do the first time, but only requires one additional year forevermore).  I send that query to Excel, where I can quickly convert any number other than 0 in my summary fields to 1.  I then add a column in which to sum up the counts across all years to get total years given.

I hope that helps.  If you need further detail about any of these steps, let me know.

One alternative to Jason's idea is to do the export he describes, but instead of converting the numbers to 1 and using the count function - use the countif function and you can get a count of all columns greater than 0 without doing any converting.