Create a constituent query of each possible combination and merge the queries using the OR operator:

Note: The following instructions use the example of querying on constituents that gave at least four times in the past five years (2000 to 2004).

  1. Create a constituent query (Query A) and filter on:
    Summary Information, Summary for Gift, Total Amount greater than 0

    On the Filters tab in the Edit field Criteria screen, select Gift Information, Gift Date between 1/1/2000 and 12/31/2000

    AND Summary Information, Summary for Gift, Total Amount greater than 0

    On the Filters tab in the Edit field Criteria screen, select Gift Information, Gift Date between 1/1/2001 and 12/31/2001

    AND Summary Information, Summary for Gift, Total Amount greater than 0

    On the Filters tab in the Edit field Criteria screen, select Gift Information, Gift Date between 1/1/2002 and 12/31/2002

    AND Summary Information, Summary for Gift, Total Amount greater than 0

    On the Filters tab in the Edit field Criteria screen, select Gift Information, Gift Date between 1/1/2003 and 12/31/2003

  2. Create a constituent query (Query B) and filter on:
    Summary Information, Summary for Gift, Total Amount greater than 0

    On the Filters tab in the Edit field Criteria screen, select Gift Information, Gift Date between 1/1/2000 and 12/31/2000

    AND Summary Information, Summary for Gift, Total Amount greater than 0

    On the Filters tab in the Edit field Criteria screen, select Gift Information, Gift Date between 1/1/2001 and 12/31/2001

    AND Summary Information, Summary for Gift, Total Amount greater than 0

    On the Filters tab in the Edit field Criteria screen, select Gift Information, Gift Date between 1/1/2002 and 12/31/2002

    AND Summary Information, Summary for Gift, Total Amount greater than 0

    On the Filters tab in the Edit field Criteria screen, select Gift Information, Gift Date between 1/1/2004 and 12/31/2004

  3. Create a constituent query (Query C) and filter on:
    Summary Information, Summary for Gift, Total Amount greater than 0

    On the Filters tab in the Edit field Criteria screen, select Gift Information, Gift Date between 1/1/2001 and 12/31/2001

    AND Summary Information, Summary for Gift, Total Amount greater than 0

    On the Filters tab in the Edit field Criteria screen, select Gift Information, Gift Date between 1/1/2002 and 12/31/2002

    AND Summary Information, Summary for Gift, Total Amount greater than 0

    On the Filters tab in the Edit field Criteria screen, select Gift Information, Gift Date between 1/1/2003 and 12/31/2003

    AND Summary Information, Summary for Gift, Total Amount greater than 0

    On the Filters tab in the Edit field Criteria screen, select Gift Information, Gift Date between 1/1/2004 and 12/31/2004

  4. Create a constituent query for each of the remaining combinations.
  5. Merge each of these queries together using the OR operator. (The order of the queries does not matter.)

    For example, merge Query A and Query B using OR. This results in Query 1. Merge Query 1 and Query C together. This results in Query 2. Repeat the process for each set of queries.

OR

  1. Create a query of constituent s who gave during the last five years:
    1. Create a constituent query.
    2. On the Criteria tab, select Gift Date greater than [the earliest date of the five-year date range].
  2. Export total gift amounts for each of the five years:
    1. Create a constituent export in Excel format.
    2. On the Output tab, select: Summary Information, Gifts, Total Gift Amount - On the General tab, select the date range for the first year.
      Summary Information, Gifts, Total Gift Amount - On the General tab, select the date range for the second year.
      Summary Information, Gifts, Total Gift Amount - On the General tab, select the date range for the third year.
      Summary Information, Gifts, Total Gift Amount - On the General tab, select the date range for the fourth year.
      Summary Information, Gifts, Total Gift Amount - On the General tab, select the date range for the fifth year.
    3. Click Export Now.
  3. Open the export file in Excel.
  4. Insert a formula into the next available column to count the cells in each row that contains data. An example of the formula for the first row is

    =COUNTIF(A2:E2,">0")

  5. To apply this formula to the whole file, copy the cell from where the formula was created and highlight the whole column until the end of the file where data entry stops.  Paste the formula and this should keep the formula but change the range for each row.
  6. Sort the spreadsheet by that last column with the greatest number at the top.
  7. Manually remove any rows that have a count less 4.

We also offer customized Crystal Report templates. To learn more about our Custom Report Solutions, contact your account manager.