On the Struggle Bus with this Query-Did not Give in last 4 years

Lauren Fardella · October 2018

I have no clue why I am having such a difficult figuring this out...but I need to NOT include anyone who has not given in the last 4 years in my query. But I need everyone that gave this year. I have my first queries which include committees, boards, and donors to events this year and some last year. Then do I SUB out anyone who has not given between 2014-2017? WHen I do that I am removing anyone that gave this year too and a ton of board members..... Why am I struggling?

Query 1 - is all committees from this year and last year

Query 2 - all event donors for 2018 and some from last year

Query 3 - #1 OR #2

Query 4 - #3 OR All Board Members

Query 5 - #4 OR Corporate Mailing List (solicit code)

Query 6 - #5 SUB the Non Givers (query is Last Gift Date NOT BETWEEN 1/1/14 and 12/31/17)

Alan French · October 2018

If someone's last gift is in 2018, they'll be included in your Non Givers query because their last gift isn't between 1/1/14 and 12/31/17, which is why you're losing them when you do the SUB merge.

I'm not quite clear on which groups of people you want to remove if they didn't give between 2014-2017 so I may have misunderstood something, but I would change the order of your merges so that you are subtracting your non-givers from those groups before you add your committee members or whichever groups should be included regardless of whether or not they donated during those 4 years.

Denise Covington · October 2018

Your SUB query needs to be positive. So it should use the operator of between. Then SUB them out. My SUB queries are always last unless you want to be sure a full committee is included whether or not they gave during that time period.

I

Alan French · October 2018

Denise Covington:

Your SUB query needs to be positive. So it should use the operator of between.

I'm not sure, I think NOT BETWEEN was correct here - Lauren wants to identify people who didn't give during that 4-year period and then remove them from the other list.

Lauren Fardella · October 2018

I got it to work....I did a gave between 2014-2018 query. Then I used AND to merge that with my 2 other merged queries....then went from there. It worked fine.

Christine Cooke 2 · October 2018

How about doing #5 as a positive? Gave between date and date and use the AND operator

Faith Murray · October 2018

Even though this has already been resolved, I just want to point out that a judicious use of parentheses could remove the need for subbing queries. I used to merge queries constantly. Now with parentheses, I almost never have to merge a query.

This, in one query, will get you ALL 2018 donors plus your board members:

(constituent is a committee member

    (OR an event donor from 2016-17

         And meets such-and-such criteria)

    OR an event donor from 2018

    OR a board member

    OR has Corporate solicit code)

OR last gift greater than or equal to 01/01/2018

This, in one query, will get you ONLY board/committee members who gave in 2018:

(constituent is a committee member

    (OR an event donor from 2016-17

         And meets such-and-such criteria)

    OR an event donor from 2018

    OR a board member

    OR has Corporate solicit code)

AND last gift greater than or equal to 01/01/2018

... I'm not sure from your description which criteria you were looking for.

Graham Getty · October 2018

Lauren, I'd recommend making the queries you list as Query 1 (all committees), Query 2 (all event donors), Query 4 (Board Members), Query 5 (Corporate Mailing List) all separate queries. Then go into RE:Mail and pick something like mailing labels, choose "Create Output query", on segmentation tab, input the four separate queries above, run the mailing labels (which you don't need, you are only doing this to create one, massive merged query (don't worry if someone appears in all four source queries, they will only be in final, merged output query once).

Once that completes, create a separate non-givers query and subtract it from the output query you made via RE:Mail segmentation and voila.

RE:Mail - it's not just for sending things, it's great for advanced queries.

Good luck!

Marie Stark · October 2018

Graham Getty‍ If I were to use Mail to merge queries, which query would be selected on the General tab?

Graham Getty · October 2018

Marie Stark‍ ALL RECORDS on the general tab - the SEGMENT tab will make sure only the records from the specified queries listed there are used.

Marie Stark · October 2018

Thank you.

On the Struggle Bus with this Query-Did not Give in last 4 years

Comments

Categories