Hello.  I'm relatively new to writing queries.  Management has asked me to pull a list of constituent records for anyone who has ever given a gift to our Foundation.  I am supposed to take this list of constituents and check to see if they are on our newsletter list (which is marked under Solicitation Code) and if they are not, I'm supposed to add them.  I also have to make sure their constituent code is set to Donor.  I plan on exporting the information out to Excel and then to update it and then re-import it.

 I figure that I need to write a query to pull the list.  I am torn as to whether it should be a constituent or gift query and how to do it.  Any input would be appreciated.

Thanks,

Eric D. Nagle, Fire Service Programs Specialist

National Fallen Firefighters Foundation 

It would be a constituent query.

I would like to suggest however that doing this is not necessary.  Having to remember to add this type of code to every donor's record is a lot of work, prone to mistakes and unnecessary.

What we do is whenever we want to pull a list for our newsletter we pull a query of all donors in the last 5 years.  This way I am sure that everyone who is a donor is on the list and it always is pulling the most current information.  There is no reason to add this code to every record if you know that all donors in the past X years always get the newsletter.  (you do not have to set a limit to the number of years - we just do.

I have a SEND NEWSLETTER code only to be added to those records who would not get picked up in the donor code - such as Volunteers, All Board and Former Board Members, Planned Giving Prospects, etc. But that is a relatively small list. I also have a NO NEWSLETTER code for those who requested not to get it.  If after 5 years they do not become a donor or do not get the SEND NEWSLETTER code added to their record they are dropped fromt he newsletter mailing, 

Every time we pull our criteria for the newsletter they have to either A) have made a gift in the past 5 years OR B) have the SEND NEWSLETTER code.

I would highly suggest that you adopt this type of system.

The same goes for the constituent code of Donor.  There is absolutely no reason to ever have a code of donor as you can query one whether or not they are a donor based on the presence of a gift.  This is a duplicateive effort and is highly discouraged for the same reasons I mentioned earlier - a lot of work, high likelyhood of errors, and unnecessary duplicate effort.

Melissa has an excellent point about Donor constituent codes, and I couldn't agree more.  Several years ago, (when I had less authority than I do now) I was bullied into creating both Donor and Prospect constituent codes and changing thousands of records.  Needless to say, I abandoned those codes as soon as I was promoted to db manager.  What a waste of time!

Melissa,

 Thank you for answering so quickly. 

 I want to see if I got this straight.  You're saying that there is no need for a "Donor" code as long as I can pull a list of anyone with the presence of a gift - is this correct?  Also, when you pull your newsletter list, they either have to have the presence of a gift OR the send newsletter code?  Is this correct?

 I guess my only other question is, how do I pull a list of those constituents with the presence of a gift?

Thanks,

I'm laughing at this because it's exactly what we do - donor & prospect (Though those clasifications never get USED for anything) I think it's be more worthwhile if they used 'Public' because that's what I call the 'Donor' Constituency in my head. (If donor's are orgs thay are all broken out by type of org - same with employees etc. the Donor code is a catch all for those with no relationship to us (like Board Members, staff, etc.)

I'd really suggest you take some classes on querying as we can only help you through the basics on querying but it can get complicated and is a really useful skill to learn.

You would do a constituent query.  Go to the gifts section on the left.  Bring over gift amount and set the criteria to Amount greater than or equal to $.01. (one penny)

This will give you anyone who has a gift on their record with an amount greater than or equal to one penny.

You may want to add other criteria to it such as gift type if you do not want to count Gift In Kind or Write Offs. so then bring over Gift Type and set the criteria to one of and bring over cash, pay-cash, etc. until you have all the types you want.

If you want to add a date range bring over Gift Date and set the criteria greater than or equal to 2/25/2004 (today 5 years ago)

You can do more but that is the basics.

Thank you Leslie for agreeing with what Melissa said about not doing this or using these redundant codes.  This should help support my stance with management.

To all - I apologize for seeming a little slow here.  I'm just very new to supporting the database (I used to just be a data-entry person) and now I've been elevated to the DBA, for lack of a better term...

P.S. if you do not have the authority to change the process and you are being asked to change these records anyway (which may happen - rome wasn't built in a day) I suggest you use global change not export/import.  Importing is complicated.  If you can create a query of donors then simply add to it the criteria that they do not have the newsletter code and you have a query of those who need to have the newsletter code added.  Simply go to global add and add the solicit code using that query.  Constituent code kinda works the same way but it depends on where in the constituent's list you want to add the Constituent Code.

Thanks Melissa for the assistance.

 I've sat through the Blackbaud Power users class but will look for some classes on query.

The best way to convince someone of making a change is to start by agreeing on the end result or goal they want.  That is - a newsletter list every time which has on it all donors and all non-donors with the newsletter code.  If you can then show them that you can achieve that goal in a more efficient, accurate way by using a dynamic query rather than by static codes then hopefully they can be persuaded.

Have you joined the Blackbaud User Society yet?  www.blackbus.org is a group of users who maintain their own website with forums, etc.  You may want to post a question there about whether to hard code or query and you should get a lot of responses.

Good Luck.

Melissa

 I fully aggree with Melissa. But there are case where we have to use donor as contutuency code. in our org constituency code is a required field. we use donor in cases when he/she donates but doesnt belong to any of our major constituency.

For your question, You have to use the constituent query and pick solict code doesnt equal to Send Newletter and the gift date is not blank (just to bring out all the records with gifts in them). This will give you people given money and dont have 'send newsletter' solict code. Here you can doa global add but plz mind that you have to exlcude the const who didnt opt for news letter.

Second Const Query, Gift date is not blank and const code doesnt equal to Donor- this will give all the records who have gvien money but not marked as donors.

Cheers!

Good Luck Erica!  Melissa's argument re donor constituency code should be helpful.  Everyone who enters a first gift on a record has to remember to also change the constituent code.  You will know better, but if others use that code to try and find all donors, they probably will get inaccurate results.  Congrats on your promotion to dba -- I was promoted from administrative assistant to dbm a few years ago and I had to learn to politely but firmly assert my authority.  The first thing I did was edit security rights!

Hi Anbu,

I understand having a "catch all" constituency code for individuals who do not belong to any other major constituency (for us, those are folks who have no direct relationship with the organization other than they are donors or prospects).  But I'm wondering why your catch all code has to be "Donor?"  What do you call non-donors who don't belong to one of your major constituencies?  We  use "Individual," then when we need a list of all donors (or non-donors) we simply run a query.

I agree that the term donor is probably not best.  We too use Individual for all those who fit into no other category.  Using donor and not globally adding to every constituent who has made a donation is misleading and can lead to users musing tha code inaccurately (despite all the training in the world)