What is wrong with my query?

Leslie Choitz

I am getting unexpected results from a constituent query.  I am trying to find people who have given money to both Fund A and Fund B since Jan. 1, 2018. 

The query I wrote is Gift Date greater than or equal to 01/01/2018 AND Gift Amount greater than $0.00 AND Fund Description one of (each) Fund A, Fund B (with the box checked for "each record must meet all specified criteria"). 

It is returning 213 too many records including people who have given to one of the funds within the date parameters, but gave to the other fund before 1/1/18. And some people whose gifts to one of the funds are valued at $0.00 i.e. inkind donations.

Where am I going wrong?


 

Mark Guncheon 2

This is tricky. I'd probably do it in export and then filter in excel.


But I was able to do it in query using a regular search for a fund within a time frame AND a summary search for the other fund with the same time frame.

I would set your query with the time frame first

(gift date greater than or equal to 1/1/2018)
AND Fund description = fund name for fund #1)


Put those in parenthesis. Then add a gift summary with the same date and the second fund and put that in parenthesis.

Try that.


Another way to do it is to create a query that pulls all donors who gave to the first fund within that time frame. Save it and then use that query in the query options as you pull the 2nd fund in that time frame


I hope this helps.


 

Katherine Manniion

Couldn't you get the results from 2 gift summary sets?


Summary for gift>gift amount greater than 0, gift date greater than 1/1/18, fund description=x

AND

Summary for gift>gift amount greater than 0, gift date greater than 1/1/18, fund description=y


I don't have data in front of me to check...but I think this is the first thing I would try

 

Karen Diener 2

I think Katherine Mannion‍ is on the right track.


It is also important to know that gift summaries in query have a set of default gift types, and Gift in Kind is one of them.  It is worth specifying the gift types YOU want summarized in your criteria, so that the software doesn't make that decision for you.


Karen

 

Christine Cooke 2

There are a couple ways to try this.

1. Gift Date Greater than or equal to Jan 1 2018 AND Gift Amount greater than or equal to $0.01 and Gift Type One of everything except leave all GIK off AND Gift Fund One of Fund A AND Fund B with checkbox


2. Two queries, one for Fund A and one for Fund B and then Merge them using AND.

Leslie Choitz

Many thanks to you all.  I'll give those suggestions a try.  I appreciate it.  And I'll quit trying to use that checkbox on the "one of" query.

Alan French

The "one of" checkbox does work well for some types of query so I wouldn't avoid using it altogether - it's very useful if you want to find constituents who have a particular combination of constituent codes, for example.