Class Giving Gift Query

Pam Gealy · April 2016

I'm trying to develop a gift query for all gifts given by a certain class (in this case it is the class of 1965). I need to use the gift query (not EXPORT), because I want one row for each fund in a split gift. My funds are categorized by 'Annual' and 'non-Annual Fund'. So, once I export this to Excel, I will summarize by fund category (I don't know Crystal Reports...)

I think the only way to get gift details by specific fund is by using a gift query (not export) We could have one gift that is split between 2 funds and one is 'annual' and one is not. So, I need to use fund, fund category and 'fund split amount' in the output of the query.

All of this works fine. Now, I need to only pull gifts from the class of 1965. I can do that by adding class year as part of the criteria (and primary_alum_info = 'yes'). BUT...what about the gifts that were given where the donor's spouse was in the class of 65? These gifts should also be included. I thought of developing a merged query. One with gifts from donor = class of 1965 and the other with gifts where donor's spouse = class of 1965. But then I could have couples that are both in class of 1965.

I'm very new to RE. So, I'm wondering if I'm overcomplicating this???

by-the-way..does anyone have suggestions for training (query and export)?

Alan French · April 2016

You could try criteria along the lines of:

(Education Class of equals 1965

AND Education Primary Alumni Information equals Yes

OR Spouse Education Class of equals 1965

AND Spouse Education Primary Alumni Information equals Yes)

Using education in a gift query is likely to give you a whole bunch of unwanted duplicates, so if you're dead set on using query rather than export then I'd recommend including the gift ID in the output so that you can deduplicate in Excel.

Pam Gealy · April 2016

Hi Alan,

Thanks for the tip. I would prefer to
user Export. However, I don’t understand how I can ‘export’
gifts, so that split funds show up in separate rows.

Thanks again!

Pam

Pamela L. Gealy

Director of Advancement Data
Services

Westminster College | Old Main –
Suite 104

319 S. Market Street | New
Wilmington, PA 16172

Office: 724.946.7991

gealypl@westminster.edu

Alan French · April 2016

I don't think you can export gift splits on separate rows straight to Excel (besides using Query), but if you're familiar with Access then you could choose one of the Access export formats and write a query in Access to display the data in that format and then export the query results to Excel. Probably a bit of a faff to set up though.

JoAnn Strommen · April 2016

Using Export the different funds wouldn't show in separate rows but in separate columns. Is there a reason you need in separate row? Don't have time to test and we don't have split gifts so actually can't test but I would think it should work. Just choose to export more that one fund per record.

Regarding training - did BB Query Essentials and Query Beyond Essentials years ago (maybe renamed now) as well as import Essentials. These were some of the most useful online trainings I've done.

PS - don't work for a school but wondering why an alum would not have their own record in RE and only be relationship record. Am sure you're not alone in entering data that way - we all do what works for our org.

Alan French · April 2016

JoAnn Strommen:

PS - don't work for a school but wondering why an alum would not have their own record in RE and only be relationship record. Am sure you're not alone in entering data that way - we all do what works for our org.

I had assumed that they do both have their own records, it's just that a constituent might have made a gift when it's actually their spouse who graduated during the year in question (and presumably isn't soft credited).

Pam Gealy · April 2016

Yes... the spouse does have his/her own record. But, may not be the 'donor' and we have historical issues with soft credit, so I can't always rely on that until we get it cleaned up. So, I need gifts where the 'spouse' is in the class, even though the spouse is not the donor on the gift. I think exporting the query results and including the gift ID, the de-duplicating will work.

The reason that I want each fund in it's own row is so that I can do summaries (probably pivot tables).

my columns => FUND ID, FUND CATEGORY, SPLIT GIFT AMOUNT

Then I can pivot on fund category and summary by split gift amount to know how much the class of 1965 gave to a certain fund category. If I had the funds spread across multiple columns on the same row, I don't think I'd be able to pivot/summarize by fund category. Every fund has it's own category. So, each gift could be spread agains multiple cateogories.

Bob Wiebe · April 2016

Does the Canned Report Alumni Class Analysis offer any help?

Christine Cooke 2 · April 2016

Write a constit query Primary Education Class of = 1965 ( please note - if you have a married couple and both spouses are in the clas sof 1965, you only need to use the Prime Education Class of and you will get both of them, If one is one Class of 1965 and the other in 1968, you will get one of them)

go to Financial Reports --> Gift Detail and Summary Report

On that report in Tab 1 include the Class of 1965 query you just made.

Credit the donor

Include these constituents (leave them all checked.

Report type Detail

Filters Tab:

If you have particular Funds that you want to see and exclude the rest then include them on this tab.

Gift Types Tab:

Standard reporting is that you are looking at the Cash/Stock/Pledge gifts and NOT the

Pay-" gift types, because those are payments on a pledge and muddle up the works. OR choose to use the "pay-" items and exclude pledge.

Columns Tab:

Choose which info you want to show up on your report.

click Preview button. From there you can export the query into Excel and it will have a separate line for every gift with the amount.

That sounds like what you are looking for.

Class Giving Gift Query

Comments

Categories