How to exclude constituent who has only given to a specific fund.

Jamie Lydenberg · September 2016

Hi, I am struggling with a query. So I am pulling all individuals who have given at least two or more gifts in the past three years above $25. What I am struggling with is removing constituents who have only given to a certain fund, for sake of explination I will call it Fund X. So I want to exclude constituents whose gifts have only gone to Fund X, but I don't want to exclude constituents who have given to Fund X and other funds during that timeframe. Is that possible? Any suggestions? Thank you!

Karina Trujillo · September 2016

Jamie Lydenberg:

Hi, I am struggling with a query. So I am pulling all individuals who have given at least two or more gifts in the past three years above $25. What I am struggling with is removing constituents who have only given to a certain fund, for sake of explination I will call it Fund X. So I want to exclude constituents whose gifts have only gone to Fund X, but I don't want to exclude constituents who have given to Fund X and other funds during that timeframe. Is that possible? Any suggestions? Thank you!

Are you doing this in the summary fields? If so you can include the criteria as "Fund ID does not equal Fund X"

Jamie Lydenberg · September 2016

Karina Trujillo:

Jamie Lydenberg:

Hi, I am struggling with a query. So I am pulling all individuals who have given at least two or more gifts in the past three years above $25. What I am struggling with is removing constituents who have only given to a certain fund, for sake of explination I will call it Fund X. So I want to exclude constituents whose gifts have only gone to Fund X, but I don't want to exclude constituents who have given to Fund X and other funds during that timeframe. Is that possible? Any suggestions? Thank you!

Are you doing this in the summary fields? If so you can include the criteria as "Fund ID does not equal Fund X"

I am using the summary field, it keeps excluding individuals who give to Fund X and other funds. I just want to remove those who only give to Fund X. Thank you!

Marie Stark · September 2016

Jamie Lydenberg:

Hi, I am struggling with a query. So I am pulling all individuals who have given at least two or more gifts in the past three years above $25. What I am struggling with is removing constituents who have only given to a certain fund, for sake of explination I will call it Fund X. So I want to exclude constituents whose gifts have only gone to Fund X, but I don't want to exclude constituents who have given to Fund X and other funds during that timeframe. Is that possible? Any suggestions? Thank you!

I have a similar situation. I have a query which says : Fund Description = X and Fund Description not one of (List every other fund). It is a consitutent query. Then I merge the queries and sub this query.

Faith Murray · September 2016

Yes... or, you can simply include Summary criteria Fund "one of" (every other query besides Fund X) along with your 2+ gifts between 2013-2016 criteria. No need to "does not equal" Fund X at all. That way there's no subbing out multiple queries.

shani traum · September 2016

Jamie Lydenberg:

Hi, I am struggling with a query. So I am pulling all individuals who have given at least two or more gifts in the past three years above $25. What I am struggling with is removing constituents who have only given to a certain fund, for sake of explination I will call it Fund X. So I want to exclude constituents whose gifts have only gone to Fund X, but I don't want to exclude constituents who have given to Fund X and other funds during that timeframe. Is that possible? Any suggestions? Thank you!

As others have suggested, I would build a query of those who gave (using summary giving) to Fund X, gifts over $25 each) AND those who gave (summary # of gifts) same criteria with # greater than/equal to 2 AND (summary giving) giving to all other funds during that time = 0.

That might work.

Shani

Madeleine Marchaterre · September 2016

Faith Murray:

Yes... or, you can simply include Summary criteria Fund "one of" (every other query besides Fund X) along with your 2+ gifts between 2013-2016 criteria. No need to "does not equal" Fund X at all. That way there's no subbing out multiple queries.

Could you give some clarification of what you mean by "(every other query besides Fund X)? Not sure how this would work. Did you mean every other fund? I have wrestled with the same issue of trying to subtract one fund out and it doesn't seem consistent because it seems to want to sub out givers to Fund X who also give to other funds. And the idea to use the following query (Fund Description = X and Fund Description not one of (List every other fund)) and subtract out the query seems unwieldy when an org has hundreds of funds. Any insight appreciated.

Christine Cooke 2 · September 2016

Jamie Lydenberg:

Hi, I am struggling with a query. So I am pulling all individuals who have given at least two or more gifts in the past three years above $25. What I am struggling with is removing constituents who have only given to a certain fund, for sake of explination I will call it Fund X. So I want to exclude constituents whose gifts have only gone to Fund X, but I don't want to exclude constituents who have given to Fund X and other funds during that timeframe. Is that possible? Any suggestions? Thank you!

All you have to do is include not only the Amount in your Query but the Fund(s) that you WANT to be included. By doing that, the folks that gave to ones you do NOT want will not be there.

Faith Murray · September 2016

Madeleine Marchaterre:

Could you give some clarification of what you mean by "(every other query besides Fund X)? Not sure how this would work. Did you mean every other fund?

Oops. Yes, I meant every other Fund. Good catch.

Yes, it should be relatively simple. You would only need one criteria for the Query:

Summary for Gift -- Total number of Gifts greater than or equal to 2

and then on the Filter tab of the same Summary box, Gift date between 01-01-2013 to current date, and Gift amount greater than or equal to $25, and Fund Description one of ...

After clicking "one of" it pulls up a selection box of all Funds. Simply click any single fund, then click the double arrow to transfer ALL funds to the right-hand box. Then go back and unselect Fund X. Even with hundreds of Funds, this should only take a couple of mouse clicks to arrange.

Let me know if this works for you! It should be simpler than merging queries or trying to coordinate parenthetical AND/OR operators.

Madeleine Marchaterre · September 2016

Faith Murray:

Madeleine Marchaterre:

Could you give some clarification of what you mean by "(every other query besides Fund X)? Not sure how this would work. Did you mean every other fund?

Oops. Yes, I meant every other Fund. Good catch.

Yes, it should be relatively simple. You would only need one criteria for the Query:

Summary for Gift -- Total number of Gifts greater than or equal to 2

and then on the Filter tab of the same Summary box, Gift date between 01-01-2013 to current date, and Gift amount greater than or equal to $25, and Fund Description one of ...

After clicking "one of" it pulls up a selection box of all Funds. Simply click any single fund, then click the double arrow to transfer ALL funds to the right-hand box. Then go back and unselect Fund X. Even with hundreds of Funds, this should only take a couple of mouse clicks to arrange.

Let me know if this works for you! It should be simpler than merging queries or trying to coordinate parenthetical AND/OR operators.

Ok, tried it, but when I pull up Fund Description I don't get a box, but a field that needs manual entry. This is really interesting to me--I tried it with an individual query and fund query to see if that made a difference but, no, it doesn't. I'm curious about your RE setup. We have 7.95, SQL Server, Standard, not hosted. I can investigate this further with Blackbaud. I have done queries where I try to sub out certain funds but not convinced of 100% accuracy of gifts. The numbers of gifts as >=2 might play a role here. Thank you for your response!

JoAnn Strommen · September 2016

Did you change the fund description to "one of"? That should bring up the list. If it defaults to equals, it's a manual enter or search box.

How to exclude constituent who has only given to a specific fund.

Comments

Categories