[Katrina Brown]

I have been asked to identify donors who have given a total of $5,000 or more over any 5 year period. (I hope I said that right). I am not sure how to begin. I cannot think how to get the five year range without querying total gift amount for each five year range possible in our records.

I am hoping there is an easier way. Any suggestions?

[Melissa Siobhan]

Quote:

Originally Posted by Katrina Brown

I have been asked to identify donors who have given a total of $5,000 or more over any 5 year period. (I hope I said that right). I am not sure how to begin. I cannot think how to get the five year range without querying total gift amount for each five year range possible in our records.

I am hoping there is an easier way. Any suggestions?

There is not a way within RE to do such a query but if you know SQL you can probably write a query in there to do this.

[DrewAllen]

It is certainly possible to do this in SQL. How depends on exactly what you mean by "any five year period." I assume that you mean any five consecutive fiscal years, but it could possibly mean ANY five year period such as the period June 12, 2004 - June 11, 2008.

Even if you do know SQL, the solution is not obvious. If you want to explore the SQL option let me know.

Drew

[stancliff]

Quote:

Originally Posted by DrewAllen

It is certainly possible to do this in SQL. How depends on exactly what you mean by "any five year period." I assume that you mean any five consecutive fiscal years, but it could possibly mean ANY five year period such as the period June 12, 2004 - June 11, 2008.

Even if you do know SQL, the solution is not obvious. If you want to explore the SQL option let me know.

Drew

This type of analysis is also on my list of learning objectives.

[Katrina Brown]

SQL is beyond me at this time. So I think I will try to work the Director of Development to narrow this down a bit.

Thank you for the offers of help and guidance.

[lcallah]

As long as the 5 year period is consecutive - you should be able to pull the information right out of RE using Query. For your parameters use the following:

Summary Information>Summary for Gift>Total Amount of Gifts
Greater or Equal to $5000.00 AND
Summary Information>Summary for Gift>Consecutive Years Giving
Greater or Equal to 5

Lisa Callahan
Database Manager
J. Sargent Reynolds Community College
Richmond, VA

[DrewAllen]

Quote:

Originally Posted by lcallah

Summary Information>Summary for Gift>Total Amount of Gifts
Greater or Equal to $5000.00 AND
Summary Information>Summary for Gift>Consecutive Years Giving
Greater or Equal to 5

This won't work for a number of reasons. If I give one $5000 gift, I should be pulled, because I have given $5000 within a five year period, but my consecutive years of giving will only be 1.

Similarly, if I give $2000 every other year, I will have given $6000 within a five year period, but my consecutive years of giving will STILL only be 1.

Conversely, if I gave $500 every year for the last 10 years, my consecutive years of giving will be 10 and my total giving will be $5000, but I will have only given $2500 within any five years and should not be included.

You have to be very careful with queries, because it is very easy to come up with something that will give you results, but does not answer the question that is being asked. That's why we usually sit down with people making requests so that we can ask them what they really want.

Drew

[lcallah]

That makes perfect sense. Thank you for steering me in the right direction.

My bad.

[mwittman]

Quote:

Originally Posted by Katrina Brown

I have been asked to identify donors who have given a total of $5,000 or more over any 5 year period. (I hope I said that right). I am not sure how to begin. I cannot think how to get the five year range without querying total gift amount for each five year range possible in our records.

I am hoping there is an easier way. Any suggestions?

I wrote a custom data analysis application (in MS Access) that takes Raiser's Edge gift record output - you may want to try, I think it will accomplish just what you need. I added it to Shared Files:

http://www.blackbus.org/forum/downlo...?do=file&id=77



It's released as open source - any feedback is appreciated. Below is from the Readme.txt:
________________________________________________________

# Consecutive Giving Total Search ("Consecutive Giving Total Search.mdb")
# By Micah Wittman, 2008
# Released under the MIT and GPL licenses (see MIT-LICENSE.txt and GPL-LICENSE.txt)

"Consecutive Giving Total Search" is an MS Access Database-based application developed to do a complex reporting
task on data exported from The Raiser's Edge.
It is designed to analyze donor gift records to determine which donors reached a giving total threshold within
a certain year interval. (For instance, the application builds a set of results that can answer the question:
Which organizations/individuals gave gifts totaling at least $10,000 within any 3-year period since 1990?).


## GETTING STARTED

Step 1 - Create a Query[1] in Raiser's with the following Output Fields:

Constituent ID
Name
Gift Amount
Gift Date
Gift Import ID

[1] Specifically, a Constituent Query with criteria that selects donors amongst whom you wish
to search for who donated a minimum total in a certain interval of years.
The included "Gifts Minimum Lifetime.MDB" file contains a hand-full of fake data records
to play with. Over-write this file with your real data as described above.

Step 2 - Save above query with name: "Gifts Minimum Lifetime" (w/out the quotes)

Step 3 - Export to file (MS Access format) the data directly from this query.

Step 4 - Save the exported file, "Gifts Minimum Lifetime.MDB", to "C:\" (your hard drive root)

Step 5 - Place the "Consecutive Giving Total Search.mdb" application in "C:\"

(Steps 4 & 5 use "C:\" to keep things simple, but if you're so inclined, you can put the data file
and the application file in any location you choose, but you must make an adjustment in
"Consecutive Giving Total Search.mdb" so it has linked table "Gifts Minimum Lifetime" from
file "Gifts Minimum Lifetime.MDB".)

Step 6 - Now open "Gifts Minimum Lifetime.mdb" and try go to it.


## Example Application Usage:

==> Begin Year: 1990, Year Interval: 5, Gift Sum Minimum: $5,000 <==

Would find all donors with gifts given between 2000 to Current Year
where total giving within a 5 year time-frame reached $5,000 or more.

NOTES:
- A Donor meeting the Consecutive Giving Total criteria will only be added to the
results table once (earliest Year Interval in which they qualify.

- Example Case: John Doe gave $2,500 gift in 1996 and $2,500 gift in 2001.
The Results Table will show GiftTotalMeetsMin=$5,000, IntervalBeginYear=1990,
MaxGiftYearInFirstQualifiedInterval=2001. His gifts in 1989 and older are not
considered and any gifts totaling $5000+ he may given in 1996 or later are not
reflected in the results. Run a standard gift summary report on donors of interest
to see a complete view of gift totals).