I'm a planned giving person who is looking for my most "loyal" donors. I'm trying to create a group of individuals who have given a gift to us in 8 out of the last 10 years (97, 98, ... 06) and they have to have given in 05 and 06. The suggestion by RE support suggests merging a multitude of queries. Does anyone have any other suggestions?

You might try using one query to capture anyone who has given a gift within the 10-year date range and a minimum number of gifts =>8. Then export the data to an Excel file with 10 gift summary columns, one for each year. You can easily sort the records and delete those that don't meet the 8 out of 10 year criteria. After cleaning up the data in the spreadsheet, you can create an import file to add an Attribute for this special group. It's a little complicated ... I can explain further if you wish. Kathleen The Bryn Mawr School [Email Removed]

I always preface my replies with - we just acquired RE7 and have not finished the conversion. Having said that, couldn't you just use the "Consecutive Years Report" under the Analytical Reports and used the detail tab to put your year limit? Thank you,
Belinda Goodman

Consecutive years is literally consecutive and gives no allowance for someone missing a year which is not the same as 8 out of 10.

Where I was working previously my IT department head was able to do this query in SQL. He exported all the data he needed and wrote a SQL script to find those who gave in 7 out of the last 10 years and and at least one of the past 3 years. I can't exactly tell you how he did it. Melissa S. Graves
Director of Development Services
The Village for Families and Children
mgraves @ villageforchildren.org
www.villageforchildren.org

GET ON THE BUS! Check out what the Blackbaud User Society is talking about today at www.blackbus.org!

If you're going to do it in SQL, there's no need to do an export, you can just run it directly off of the RE back end. You will need a formula to calculate the fiscal year, which will vary depending on when your fiscal year starts. If you start on July 1, then you can use Year(DateAdd(qq,2,Gift.DTE)) Here is the query. SELECT Constit_ID FROM Gift WHERE Gift.DTE Between {your FY97 start date} AND {your FY06 end date} AND Constit_ID IN ( SELECT Constit_ID FROM Gift WHERE Gift.DTE Between {FY05 begin} AND {FY05 end} ) AND Constit_ID IN ( SELECT Constit_ID FROM Gift WHERE Gift.DTE Between {FY06 begin} AND {FY06 end} ) GROUP BY Constit_ID HAVING Count(Distinct {FY Formula})) > 8 There are other ways to write this formula that might be more efficient, but this is probably the simplest to understand (assuming that you have a passing knowledge of SQL). --- Edited to fix problems with the interpretation of angle brackets. Drew J. Drew Allen Children's Hospital of Philadelphia [Email Removed] Check out the user run RE Forum at http://www.blackbus.org/ . Check out the Crystal Reports blog "Crystal Clear" at http://www.blackbus.org/blogs/viewblog.php?userid=63  --- Edited at 3/22/2007 9:32:50 AM by J. Drew Allen

Hi - I am in the position of having to develop a good list of planned gift prospects.  We want to see those individuals who have given at least 8 years (but not necessarily consecutively).  I've looked at Bryn Mawr's method, but it seems very time consuming (we want to go back to 1988!).  Are you aware of any other way I can get at what I'm looking for?  Thanks for your time.  Denise Sanders, Goodwill, Colorado Springs. 

Hi Denise!  I had to do something similar 3 years ago.  I put together a pretty good query that tends to be a tad bit over-inclusive but it's better than the alternative.  What's your email address?

 

 

dsanders@goodwill-colosprings.org 

Thanks, I look forward to seeing it!

Denise - I do this kind of thing in Excel; but if Drew's process is too time consuming for you, I'm afraid my way would be even more so. Essentially, what I do is export each fiscal year total using Summary of Total Number of Gifts. Then in excel I used a funtion to calculate who has the least amount (I think we looked for at least 5 gifts in the last 10 years?). Let me know if you want more details. Again, very time consuming - but it worked!

Hi Robin,

 I saw the post you made over a year ago regarding consecutive year gifts.  Our organization is hoping to pull a list of donors who have given for the past 20+ years (but not necessarily consecutively, they may skip a year or two).  Can you send me the query you created for consecutive year gifts?

 My e-mail address is mstitzinger@some.org.

 Thank you for your help!