Total Giving Query

Jonathan Champ 2

Hello,


I am trying to pull a query on a major gift officer's (MGO) portfolio. Part of the output items is total amount of gifts given for each constitutent in the MGO's portfolio.I know that in RE under Prospect>General>Total Given it lists a consituent's cumulative giving. I am currently having a hard time trying to find this within query. Does anyone know the best way to pull this?


-Jonathan

Gina Gerhard

Jonathan -


Unless the total amount of gifts is part of your query criteria, then you should not be pulling total gift data in the query itself -- but in an export. Or am I missing something?  

• Sounds like your query should find the constituents in your MGO's portfolio
• And then you want to run that query through an export to show the actual total from each of those constituents (in Summary section of fields).  
• The underlying mantra = never export gift data from a query

Jen Claudy

Gina is correct, and you'll see it posted everywhere, "Query is a grouping tool."  However, I often export from a query when I need something quick & dirty or when I need to be able to also access records from that query.  I would only share that exported Query data with others if I had reviewed it very closely to be sure it's all correct before doing so.  Sometimes, I just don't have the time to build a Query and then an Export, when it's data that I alone am using.  (I also often export "raw data" from a Query and then work with it in MS Access, but again, I closely review and examine the results.)


If you truly need to stick with Query, or you want to double-check data before you use Export, look at the bottom of the left pane on the Output Tab and use the Summary Fields.  There are Summary Gift Fields that should give you what you're looking for.  Be careful, though, because the Gift Summaries often (always?) select Gift Types for you, hidden behind the scenes.  So prevent this from causing you headaches and always include Gift Type one of in your Summary Field Criteria.

JoAnn Strommen

Ditto what Gina said.  You may be looking for the summary info where it isn't. The summary info is not availablel in a gift query/export.  It is an option in constituent query/export.

Gina Gerhard

Beware of gift data!!  There are gift types and the dreaded soft credit issue that always have to be specified ...

Jen Claudy:

Gina is correct, and you'll see it posted everywhere, "Query is a grouping tool."  However, I often export from a query when I need something quick & dirty or when I need to be able to also access records from that query.  I would only share that exported Query data with others if I had reviewed it very closely to be sure it's all correct before doing so.  Sometimes, I just don't have the time to build a Query and then an Export, when it's data that I alone am using.  (I also often export "raw data" from a Query and then work with it in MS Access, but again, I closely review and examine the results.)


If you truly need to stick with Query, or you want to double-check data before you use Export, look at the bottom of the left pane on the Output Tab and use the Summary Fields.  There are Summary Gift Fields that should give you what you're looking for.  Be careful, though, because the Gift Summaries often (always?) select Gift Types for you, hidden behind the scenes.  So prevent this from causing you headaches and always include Gift Type one of in your Summary Field Criteria.

 

Joy park

I am trying to pull a query on a major gift officer's (MGO) portfolio. Part of the output items is total amount of gifts given for each constitutent in the MGO's portfolio.

"... you need the Total Amount of...."


Stop!  Save your Query at its most basic level and use it to refine an Export.  Exit out of Query!  You need Export!  The Summary fields will do this for you!  You can specify a date range and limit it to certain Gift Types, certain Constituent Codes, etc.   You can base your Export on all records, or a selected Query of records, or just one constituent record - like your example.


Just make sure you have Soft Credit as an Export field - if your organization uses SC!  You may need it to "tease gifts out of hiding" (like United Way, as an example) that have been SC'd to the donor but not necessarily by their Personal Cheques.  :)


And yes, as Gina mentioned, watch your Gift Types.  If you are using any Pledges, Major Gifts, Recurring Gifts, Tribute Gifts, etc. then you need to remember to include the appropriate Gift Types/ "Pay-Cash"!

Zane Magnuson 2

Joy park:

I am trying to pull a query on a major gift officer's (MGO) portfolio. Part of the output items is total amount of gifts given for each constitutent in the MGO's portfolio.

"... you need the Total Amount of...."


Stop!  Save your Query at its most basic level and use it to refine an Export.  Exit out of Query!  You need Export!  The Summary fields will do this for you!  You can specify a date range and limit it to certain Gift Types, certain Constituent Codes, etc.   You can base your Export on all records, or a selected Query of records, or just one constituent record - like your example.


Just make sure you have Soft Credit as an Export field - if your organization uses SC!  You may need it to "tease gifts out of hiding" (like United Way, as an example) that have been SC'd to the donor but not necessarily by their Personal Cheques.  :)


And yes, as Gina mentioned, watch your Gift Types.  If you are using any Pledges, Major Gifts, Recurring Gifts, Tribute Gifts, etc. then you need to remember to include the appropriate Gift Types/ "Pay-Cash"!

 

And while you're at it, here's a really useful tip for Exports that I missed for years - When you're adding fields to the Output of an Export, you'll notice an item at the very top of the list named "New Constituent Export" or something like that with a computer-spitting-out-a-disk icon. If you make click on that to highlight it before adding new criteria, you can add multiple copies of the same field that have different criteria.


For example, if you need to spit out different summary fields, such as "FY16 Gifts" vs "FY15 Gifts" or "Pledges" vs "Non-Pledges", you can use that trick to export multiple Summary fields with those different criteria.

Example 1: Here, I just clicked "Total Gift Amount" twice and got this result. You cannot set different criteria in this setup


Example 2: Here, I clicked on "New Constituent Export" before adding the second Summary field. In this case, you can set different criteria for each of the output fields.


 

JoAnn Strommen

Just to clarify what Zane posted... the "New Constituent Export" or something like that  will actually display name of export if you have saved/named it. 


You can also choose multiple copies of the same field by licking and highlighting on heading - in his example "Summary Information" - you can put multiple total gift amounts as he described.  I find that handier as the top of my output list is often off screen and it's easier to pick the heading that scrolling up.