Total Years of Membership

Spencer Jansen

I am trying to provide recognition for long-time members, those with membership of 25 years or more. The membership does not have to be consectucitve just cummalitive. If you use the "Total Years As Member" in the query it just subtracts the joined on date from todays date and determines that as the total years as a member. For example: John Smith's Joined on date is 1/1/1985. He renewed in 1986,1987,1988 but dropped in 1989. He then Rejoined in 2011 and has been a member ever since. The query would list John Smith as being a member for 30 years, but we know that is not true. Any suggestions?



Thank You,



Spencer Jansen

Membership Manager

Arkansas Arts Center

501 East 9th Street

Little Rock, AR  72203

501.396.0337 w

501.626.8849 c

sjansen@arkansasartscenter.org

 

James Andrews

Maybe do a membership export, export the history transaction date and type, and count them in excel?   A pivot table would make pretty quick work of that.  Come to think of it, maybe you could even use a pivot report in RE if you don't want to go through the trouble of exporting.  I'm not super familiar with how that might work but it might.

John Heizer

Membership reports get really tricky.  What do you consider a "year"?  Calendar year?  Fiscal year?  365 days of membership?



Another big question is how clean is your data?  Going back 25 years means your data has gone through lots of changes.  I've worked with RE since 1995 and the old DOS version 5 handled Memberships as a special type of Gift record that didn't always convert cleanly to the first Windows version 6.  If your Membership data came from a different program it could be better condition, or could be worse.  Have you had a lot of duplicate Constituents that have been merged but the Membership history not merged?



With all that in mind I think James Andrews is on the right track.  You know that folks you're looking for have to have joined before 1991, so that could help narrow it down but, unfortunately, you can't use a Constituent Query as the source ("Select records from...") for a Membership Query (goes back to the problem of duplicates who haven't had Membership history merged where the 2nd "Joined on" date wouldn't fall into the pre-1991 range).



Ah ha! Working through this message I may have stumbled on at least a partial solution.  Try using the "Total Times Renewed" >= 24.  The first year is always "Joined," so that "Joined" year plus 24 renewals would give you folks who have been Members for 25+ years.  You'd still have to manually deal with Constits with multiple Memberships, but unless you've got pristine data I think you're going to have to massage the results no matter what.

Spencer Jansen

John,



The "Total Times Renewed is greater than or equal to 24" is the best I could figure out so far, but there are some that are left out because it does not factor in the "rejoins". Since this is a subject that can quickly create a poweder keg if someone is not recognized when they should be we want to be absolutely accurate. It really baffles me that this is such a hard thing to ask RE to do. Both of my tech supports yesterday couldn't find a solution, and one even said it is too hard for the software to figure out!



My other thought was to run a query of those who have given to the membership fund for 25 or more years, but unfortunately they have changed that up a couple of times.

James Andrews

John Heizer:



With all that in mind I think James Andrews is on the right track.  You know that folks you're looking for have to have joined before 1991, so that could help narrow it down but, unfortunately, you can't use a Constituent Query as the source ("Select records from...") for a Membership Query (goes back to the problem of duplicates who haven't had Membership history merged where the 2nd "Joined on" date wouldn't fall into the pre-1991 range).

haha sadly I've realized that my answers are almost always 'get the data out of RE and analyze it elsewhere.'