Giving history and total gifts

Victoria Kingstone

Hi folks

I am trying to run some queries to bring up some specific groups of people but I cannot figure out how to achieve them.

1) have donated for more than 3 years (but not necessarily consecutively)

2) with at least one gift of £25 or several gifts per year totalling £25


If there is a last gift date of not before 01/01/2014, I can possibly say last gift date greater than or equal to 01/01/2014, then 1st gift less than 01/01/2011. This would pick up everyone who'd given more than 3 years wouldn't it?


Still flumoxed on the gift per year total though because if I do Summary info, total amount of gifts, wouldn't this bring up the complete total, not just the total for each year?


Any suggestions please?!



 

Alan French

If you search the forum for "consecutive years" there should be a number of other topics where people have discussed similar problems - the consensus seems to be that it's pretty much impossible to do entirely in RE, and will probably involve some work in Excel, Access etc.


If it was me doing it, I quite enjoy working in Excel so I'd probably export a list of all constituent IDs, another list of all gifts and use a bunch of SUMIFS functions on the constituent sheet to add up their gifts and work out how much each person gave in each year (one column per year). It should then be fairly straightforward to find anyone who has an amount > £0 in 3 or more columns, or >= £25 in any one column. If you're less confident with Excel, I'd be happy to mock up an example of what I mean that you could adapt to work with your data.


For me, the benefit of doing it in Excel rather than using the gift summary information available in export would be that you could use functions to calculate the date ranges rather than typing them all in by hand - handy if you want to cover a period of many years.

Victoria Kingstone

Alan French:

If you search the forum for "consecutive years" there should be a number of other topics where people have discussed similar problems - the consensus seems to be that it's pretty much impossible to do entirely in RE, and will probably involve some work in Excel, Access etc.


If it was me doing it, I quite enjoy working in Excel so I'd probably export a list of all constituent IDs, another list of all gifts and use a bunch of SUMIFS functions on the constituent sheet to add up their gifts and work out how much each person gave in each year (one column per year). It should then be fairly straightforward to find anyone who has an amount > £0 in 3 or more columns, or >= £25 in any one column. If you're less confident with Excel, I'd be happy to mock up an example of what I mean that you could adapt to work with your data.


For me, the benefit of doing it in Excel rather than using the gift summary information available in export would be that you could use functions to calculate the date ranges rather than typing them all in by hand - handy if you want to cover a period of many years.

Thank you, given the time I have, I think we'll just modify the groups we were planning to mail! Maybe next time I'll try your excel method. Thanks!

Alan French

In case anyone's interested, I've attached a spreadsheet as an example of how I'd go about working out the total years. Feel free to use it if it's useful - just need to paste in the constituent and gift data, and change the start date in B1/add some extra columns to reflect the years you want to look at.

JoAnn Strommen

Victoria Kingstone:

Hi folks

I am trying to run some queries to bring up some specific groups of people but I cannot figure out how to achieve them.

1) have donated for more than 3 years (but not necessarily consecutively)

2) with at least one gift of £25 or several gifts per year totalling £25


If there is a last gift date of not before 01/01/2014, I can possibly say last gift date greater than or equal to 01/01/2014, then 1st gift less than 01/01/2011. This would pick up everyone who'd given more than 3 years wouldn't it?


Still flumoxed on the gift per year total though because if I do Summary info, total amount of gifts, wouldn't this bring up the complete total, not just the total for each year?


Any suggestions please?!



 

Victoria, just so you know for future- if you do a Summary info total amount of gifts you can set further criteria of gift date.  So you'd pull summary info multiple times with criteria for gift date set for each time period you needed.

Judy Hudson

Hi Alan ~

 

I wasn’t able to see the Excel spreadsheet you
mention in your post as being attached.  Any chance you can
attach and send it in an email directly to me?  I would really
appreciate it!!!

 

 

Judy Hudson| Database Administrator |

The Family Place | PO Box 7999 | Dallas, TX
75209 | 

O: 214.443.7765 | F: 214.443.7744 | E:
jlhudson@familyplace.org
|

 

Where family violence stops

www.familyplace.org

24-Hour Crisis Hotline 214.941.1991

 

JoAnn Strommen

Judy Hudson:

Hi Alan ~

 

I wasn’t able to see the Excel spreadsheet you mention in your post as being attached.  Any chance you can attach and send it in an email directly to me?  I would really appreciate it!!!

 

 

Judy Hudson| Database Administrator |

The Family Place | PO Box 7999 | Dallas, TX 75209 | 

O: 214.443.7765 | F: 214.443.7744 | E: jlhudson@familyplace.org |

 

Where family violence stops

www.familyplace.org

24-Hour Crisis Hotline 214.941.1991

 

 

Judy, I just opened his attachment.  They don't come through with email posts but if you go the BB community forum page attachments can be found as part of the post.
https://community.blackbaud.com/forums/compose/quote/159/32037/117931