Multiple Single Gift Query

Kera Hamilton

OK.  I'm officially stumped.  I have a consulting firm asking for database information and, after three days, I still cannot set up the query criteria for the requested list.  They are looking for Individual Donors who have given a single gift of $250 or more in 2016 and 2017 and 2018.  They don't want cumulative gifts of 250, but single gifts.  (Looking for consistency.)   How do you ask for SINGLE gifts for CONSECUTIVE years?  Everything I put together results in Zero, Nada, Zilch.  HELP?

Alan French

You could use the summary information, which is found near the bottom of the list in a constituent query. You'll need to add a new summary for each of the three years, with a criteria of total number of gifts > 0 and filters to only include gifts with a gift amount >=$250 and gift dates within 2016/2017/2018 (plus any additional criteria you might need to include only the relevant gift types/funds etc.)


I can upload a screenshot if that made no sense...

Kera Hamilton

Thanks for the response, Alan.


But doesn't the Summary Information give the cumulative gifts for each year?  They're looking for single gifts of $250up in each of three years.  Am I interpreting my results incorrectly?

shani traum

How about...


Gift amount greater than or equal to $250 and gift date between 01/01/16 and 12/31/16

AND

Gift amount greater than or equal to $250 and gift date between 01/01/17 and 12/31/17

AND

Gift amount greater than or equal to $250 and gift date between 01/01/17 and 12/31/17


I'm not sure you need to use summary for this, just straight gift criteria.

Larry Wheeler

I do not know if you can do it with one query.  When I was asked to provide very similar information, I used a query for each year and then merged the queries.  


If there is another way to get this done, I'm assuming it would be in Reports>Financial Report> Gift Detail and Summary Reports.  I just figured the way i did to be just as quick.


Larry

 

Alan French

If you set up a query with the below criteria, you should end up with constituents who have given at least one gift of $250+ in 2016, and at least one gift of $250+ in 2017, and at least one gift of $250+ in 2018:

Because of the filters within the summary (the indented bits), only gifts more than $250 are being counted towards the total.

Kera Hamilton

Shani,  Thanks for your response.  Tried this, but come up with Zero because it seems to interpret my criteria as one gift with all three years as its Gift Date.

Kera Hamilton

Alan,

 

I am
suspecting that you are a QUERY GOD!  J  This is
definitely a different progression that I have been beating my head
against for the past three days.  Excited to try this. 
Will let you know if you have saved my life!

 

Kera

 

Alan French

You're welcome, hope it works for you. I picked up most of my query knowledge from these forums so it's nice to be able to pay it back occasionally

Kera Hamilton

Thank you ALL for your suggestions and comments!  This is the first time I have used the Community Discussions and I am a sincerely believer now.  The query is completed and reported, and I feel like someone just shot a 4 ton buffalo off my back!  Be well, all.  Youse is da best-est!