Query problem - Im missing something

suzie deaves

I am doing some analysis of our data where I am trying to identify constituents that haven't donated - ever using  the criteria of 'gift id is blank'. However, I have noticed though that a few of the constituents that havent donated (so gifts are blank) are actually recipients of a Soft Credit though another donor.  How can I adapt the query to exclude anyone who is a soft credit recipient? Looking at the fields I can't seen an obvious way of doing it. 

Using recipient soft credit id only works when I look for those constituents who have given 'gift id is not blank'.





Any thoughts?

Tom Klimchak

Hi... Assuming this is a Constituent Query you want to tell it to consider soft credit gifts as well as hard credit gifts.  You can't really do that with individual fields, but rather, with setting on the query itself.  Here's how you do it:


1. Open the query (that's easy!)

2. Click on Tools --> Query Options...

3. Click on the Gift Processing tab.

4. You probably want click on "Both" and "Both"... This controls how the query handles soft credits when running.

5. Click the OK button.


Now when you run the query those people should be removed.  Instead of using "Gift ID is blank" we typically use "Summary for Gift" = 0 with the specific criteria we need.  


Hope that gets you started!

suzie deaves

Tom

Thanks for that, I don't need to use those options much so forget they are there! - It seems to work - certainly the number of returned constituents is reduced but it is difficult to confirm it is actually doing what I want - if a field value was returned then I could do a sanity check or analysis on those records (which ideally I want to do)... a lot of this data is really old and wasnt entered in a consistent manner which means I cant rely on constituent attributes etc being set to help me..  i guess I need to some work outside RE in Excel to identify those constituents which drop off..  but this gets me a step nearer - so thanks  :-) ...


Suzie

Katherine Mannion

When I'm looking for groups of records like this, I go for "gift date is blank".  Even on a soft credit you know there will be a gift date.


Good luck!


Katherine

WIST Data Solutions

Becky Jones

The way I would start would be to run a non-donor report and click the option at the bottom to create a query that meets your criteria.  I am running another report right now so I cannot double-check but I'm pretty sure there is a capability to figure in soft credit recipients. 

John Prangl

Looks like I landed in the right spot, but I will go ahead anyway.  If you need donors in your database that have not donated, use the TOTAL GIVEN equaling zero.  You can put dates to get specific or leave it blank and it will show you anyone who has not given at all.  You could again qualify by constituency code, always have your queries marked as DO NOT DUPLICATE.  this would not happen here but it sure helps on other queries.  QUERIES ARE FUN!!!!!!!!!!!!!!!!!!!!!!  And they can DANCE

 

suzie deaves

Thanks Becky and John for the suggestions.

The report query was an interesting idea which  helps in as much as it will pick up Donors or Donors & SC but doesn't get me further forward in creating a list of those who have never donated (thats is relatively easy to do ) but also identifying which of those are a SC  recipient in the same query. I had hoped it would be as simple as adding a field to the query which I could use to filter/sort the data from an  export - so I can look at clearing out dormant constituents but not those who are SC recipients.


 

John Prangl

Good AM,

 

Is sure did try everything I know, from queries to reports and back
again, using one query in another, you ge the picture.  I
could get all constituents that ever received a SC or gave one, all
“non-donors”, and one could by hand de-dupe but that’s not the
idea.  All the stuff got my Citrix to go away but got it back
this morning and back work, sorry I couldn’t help out.  BUT I
DO ENJOY COMMUNITY!!!

John prangl