Merging Queries vs Complex Queries Best practices?

I often (particularly when pulling a mailing list) will use simple Queries and an Attribute.


I create an Attribute, usually prefixed with a number to put it at the top of the Attribute Tab, and then use Global Add and start with the top of the heirarchy.  If we want all current Board members on the list, I'll pull up my Query for that and do a Global Add (using the Attribute's Description & Comment Fields for additional information).  Then we want to include all recent donors, but because it's an appeal, we want to know when the most recent gift was, so I'll write a Query for donors from FY15 and add the Attribute, then FY14 and add the Attribute, etc. (set the Attribute to Allow Only One Per Record and the Global Add will only add it to those not already "on the list").  Once I've added everyone, then I can go thru and start either removing (Global Change provides a method of globally deleting Attributes from Records in a Query) or changing the Description/Comment to DNM (do not mail).  (I prefer to end up with Appeal Tags on all records that qualified, and use a Package of "Do Not Mail" for those that were pulled from the list...that way when someone asks why so-and-so was/wasn't on the list, I know either way.)


In the end, I can write a Query for all Constituents with this Attribute, and it provides a nice little list that really operates like a Query List (I was using this method before Query Lists were an option...and as I said, once a record is added to the list, I don't like to remove them altogether).  Then I can easily Globally Add an Appeal Tag, or Actions, or whatever, and when done with the Attribute, use Global Change to remove the Tags from all Records and have that Attribute ready to use for the next list.


For Queries that are more dynamic and need to be available over time, I generally prefer more complex over merged...  I really wish that RE would allow you to copy the Criteria (particularly of a Summary Field), though.  That would speed things up considerably.


If you use complex queries (or even merged), I suggest that you regularly test and spot check as you build the Query.  Nothing worse than getting to the end of some project and realizing that a number of records were missed because of one incorrect line of Criteria or qualifier (AND, OR, or parentheses).

I also prefer to create a single query whenever possible because after you merge the results are static.  This is fine for some things, but most of the time I prefer dynamic results.  I also don't like the limitations for creating the query criteria - no parenthesis within parenthesis for example.

As a general rule, if I have a query that has multiple AND and OR statements or where you're really requiring the use of 'nested' conditions (which query can't handle) then I start to think about merging queries.  


Also, on the issue of merging 'dropping out records' then there's some issue with either the data on the record or how you've structured your queries you're using as components or the merge operator you're using.  

• Almost always I find it's some data on the record that's causing it to drop out. One culprit is having 'Send Mail' unchecked or the Date To populated in the address - so the query may automatically drop those out ... 
• The merge is just doing what you're telling it to do, so it's usually not the culprit unless I did something wrong in the structuring of the queries being used.

What I haven't seen stated here as a 'plus' for merge queries is if you have a complex query it's often difficult for someone else to decipher. If you set it up as a merge query and name the queries part 1, part 2, takeout, etc. then it's often easier for someone coming in cold to actually figure out what you've done.  BUT if they then want to reuse the work, they have to understand about refreshing static queries.  

I mostly use merged queries when the query is already complicated and excludes records (i.e. not equal to, not one of).  It's easier to think about (and explain to the person that requested help on how to make the query) when you have one query that has the records you want, and then a separate query that 'includes' the records you don't want.  Then merge using SUB.


The other time is when querying on Prospect tab Ratings, thanks to the lack of a Prospect query type and having to use a Constituent query type.

I'm not so sure about the reason for the dropped records in merging.  We've had records in Query A and in Query B and when we merge A AND B, some will disappear.  No logical reason, but there it is.


 

Nope - they WERE in both queries, so the AND operator should have worked.


It's not large scale dropping, just one or two out of many.  But annoying just the same.


 

I will echo the voices of those who prefer to use a single query except when that is just not possible. In my opinion (and an opinion is all it really is), that's the only real use for merged queries to begin with: To get to those hard-to-reach reporting spaces that a single query can't swing.


This can also depend a lot on how the operator in question thinks, though. I've worked with some people who just never got a good grasp of query logic, and would throw up their hands at anything with more than two criteria. I've also worked with people who had no problem with query logic, but who never could get the merge types straight. I myself still have to look them up every time I resort to using them and I am still surprised every time because there are two of them I constantly confuse for one another. I look up which is which and go "Huh...not how I remember that..."





 

Nicole S:

I also prefer to create a single query whenever possible because after you merge the results are static.  This is fine for some things, but most of the time I prefer dynamic results.  I also don't like the limitations for creating the query criteria - no parenthesis within parenthesis for example.

Something I learned somewhere along the way...


If you have a merge query and you want an updated set of results:

Rerun the sub queries (the ones you're merging), run the static merge query you had already set up, and then on the result page click on "results" and then "refresh".  This will re-preform the merge but using the results from the new versions of the sub queries.


So, let's say you have Query "Number 1" and Query "Number 2" that on January 1st you merged into query "Number 3".  Now it is January 8th and you want a new version of that merged  query.  Rerun Number 1 and Number 2.  Open Number 3 and run it.  You will see the January 1st results.  Then, from the menu above the results, click on records...refresh.  You will now see the results as of January 8th (when you reran the base queries and asked for refreshed results in the merged query).


I hope that made sense!


Shani