How to query for cash for remainder when seeking singular gift records

Amanda Hoffman 2

A donor gave $1,000.00 that was applied toward a coalition pledge. The pledge balance was $480.00 so the remainig amount of $520.00 was recorded as a Cash Remainder.

I am now trying to run a query of all donors (within a specific date range) who gave $1,000.00  (non-cumulative) and this donor doesn't show in the results. I get why since it's listed as two separate entries so I was wondering if anyone has a soluction for capturing this donor in a query.  I tried adding OR Gift Summary, Total Gift amount = $1,000.00 within the same date range but that pulled in hundreds of records outside of the date range.

Increasing the original pledge amount would've solved this but unfortunelty, we don't increase coalition pledges as they need to stay true to original amount. 

Thanks for your help!

Amanda <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 

JoAnn Strommen

Amanda Hoffman:

A donor gave $1,000.00 that was applied toward a coalition pledge. The pledge balance was $480.00 so the remainig amount of $520.00 was recorded as a Cash Remainder.

I am now trying to run a query of all donors (within a specific date range) who gave $1,000.00  (non-cumulative) and this donor doesn't show in the results. I get why since it's listed as two separate entries so I was wondering if anyone has a soluction for capturing this donor in a query.  I tried adding OR Gift Summary, Total Gift amount = $1,000.00 within the same date range but that pulled in hundreds of records outside of the date range.

Increasing the original pledge amount would've solved this but unfortunelty, we don't increase coalition pledges as they need to stay true to original amount. 

Thanks for your help!

Amanda <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 

Did you put a date range in your summary criteria? You'll will still pull any gifts of $1000 by other donors. You should be able to pull your complete list using summary gift amount with the correct date range/fund/gift type as filters. Try just using summary info - I would think that should work.

Other than using summary to accomodate more than one gift payment or adjusting so that it can be applied as one gift I don't believe there's any way it will appear in a query except adding criteria of "OR specific record equals____" and basically manually include them.

Am guessing you'd have the same issue if tried soft crediting both the gifts and you used summary - probably pull others also. 

Amanda Hoffman 2

JoAnn Strommen:

Did you put a date range in your summary criteria? You'll will still pull any gifts of $1000 by other donors. You should be able to pull your complete list using summary gift amount with the correct date range/fund/gift type as filters. Try just using summary info - I would think that should work.

Other than using summary to accomodate more than one gift payment or adjusting so that it can be applied as one gift I don't believe there's any way it will appear in a query except adding criteria of "OR specific record equals____" and basically manually include them.

Am guessing you'd have the same issue if tried soft crediting both the gifts and you used summary - probably pull others also. 

Hi JoAnn,

Thanks for your response. I had a date range in the Gift Summary field but it still didn't pull the donor into the query, which I thought was odd. I'll keep trying! Before emailing the forum, I called support and their suggestion was the Gift Summary field too but it didn't work then either. I'll have to call them again!

Thank you!

JoAnn Strommen

Amanda Hoffman:

Hi JoAnn,

Thanks for your response. I had a date range in the Gift Summary field but it still didn't pull the donor into the query, which I thought was odd. I'll keep trying! Before emailing the forum, I called support and their suggestion was the Gift Summary field too but it didn't work then either. I'll have to call them again!

Thank you!

Amanda, Did you get it solved? 

Do you have other criteria in the query?  Constituency code has cause me what I thought were errors a couple of times due to start date compared to gift date.  Constit code date from was 4/4/XX.  Gift date was 4/3/XX so gift didn't pull when constit code part of criteria.

Just sharing things I've run into.....

Amanda Hoffman 2

JoAnn Strommen:

Amanda, Did you get it solved? 

Do you have other criteria in the query?  Constituency code has cause me what I thought were errors a couple of times due to start date compared to gift date.  Constit code date from was 4/4/XX.  Gift date was 4/3/XX so gift didn't pull when constit code part of criteria.

Just sharing things I've run into.....

JoAnn,

Yes, thank you! It has been resolved - but not in query....which I should've known! Agh! I had to export the query. Though the gift date criteria was very specific in the Gift Summary output, the query results still showed gifts far outside of the date range. Support said it's the same as anything else...one too many fields and query is grouping. I get that but for some reason thought this might have been an exception. Guess I was wrong! Haha. But at least it works.

Export fields were the same as in the query output. Gift Date, Gift Amount (for those that did give a one-time $1,000.00 gift) and then Gift Summary with filters of the same gift date. The $1,000.00 was listed for the donor in the "Gift Summary" column but he had a blank in the "Gift Amount" column that was for singular gifts of $1,000.00. The export requires minor clean-up/sorting but overall works great!

Thank you!

Amanda