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