Tracking intermittent membership

[memilanuk]

Hello,

New guy here, go easy on me please

I'm working on re-doing the membership database for a local club of 700+ members (the old/existing one is in M$ Works). One of the things we've never been able to track very effectively is past membership. Currently we have people check on the application when they purchase a membership whether it is 'New' or a 'Renewal' - but that doesn't tell me if an individual has say, three years consecutive membership or if he skipped a year in there along the way. I need to be able to track this due to some changes in the club by-laws involving election of officers, etc.

At the moment we just record 'Member Since', 'Date Renewed', 'Date Expires', and whether the member is a Life Member (i.e. no expiration date). Any suggestions on how to lay things out to be able to take these issues into account would be very welcome!

TIA,

Monte

 

[HiTechCoach]

Monte,

Check out this: Membership database

 

[memilanuk]

Boyd,

Thanks for the link! If I'm following along correctly here, you're using a separate table for the payments, with a discrete record for each payment/transaction - which should allow us to check if a member has made a payment each year (identifying any lapses and whether it is a renewal or not), when their first payment was (i.e. 'Member Since'), etc. A lot of the payment details are more involved than we currently need - we only take cash, no checks, no cards at this point (but that may be changing down the road).

I'm still a little fuzzy on how I should track the whether the member is a Life member or not... I have a couple associations that are essentially membership classes like you use (Life, LEO) in that they affect the dues rates (Life = 10x current annual, LEO = 1/2 annual dues), but the Life membership has basically no expiration (until the member moves away or passes on). There is another association (with a parent organization) that we track for no reasons other than informational i.e. doesn't affect a thing. Currently we have been using a sort-of boolean form for these - 'X' or ' ' (as opposed to the previous 'Y','y','X','x','Yes','YES','yes', etc. nightmare) and a blank (' ') in the DateExpires column and some ugly filtering to pull it all together (in Works).

Thanks,

Monte

 

[DCrake]

Is this a CIU membership?

My db records members subscriptions in a seperate table and it knows what the subs are based on the member type. It also records then number of consecutive years an active member. Where there are gaps in the membership it knows that by the number of times the member has paid there subs against the number of full years between the initial membership start year and the current year.

With the impending introduction of full member status for ladies coming soon we are revamping the system to accommodate this as in the past it was Male member and Wife/Partner/Spouse. Now each lady member needs her own membership number, Angel card, pass card etc. And even though they may have been on their partners membership for x years they must begin at year 1.

I don't know how you calculate life member status and whether it is up to you or the member to prove continued membership fior x years but our system tracks the lenght of membership via subs. It is not unknown for persons being detained at Her Majesty's please to continue to renue membership, and in some cases we also take member in arrears status on board.

David

 

[gemma-the-husky]

one approach would be to treat any terminated membership as closed, and allocate a new membership number on reregistration. this is a bit tricky though, if you DO need to be able to track past membership.

DC's solution is obviously fully developed, and handles this (in part) by recording an active years counter, which seems a good solution.

 

[memilanuk]

DCDrake,

Not sure I follow on the 'CIU Membership'?

We have a similar issue with the spouse/partner issue... 'technically' the membership is for one person. A number of years ago the by-laws were revamped to recognize women members, not just 'wife of a member'. Most people seem to have stuck with the 'member' bringing family members as occasional guests rather than purchase full and separate memberships for the spouse, children, etc. and that seems to work well enough for our purposes. There are a few where I think the whole dang clan has joined as separate members - great from a fundraising point of view, but a bit odd seeing that many common names with very similar (or identical) phone numbers and addresses.

I think I have the payment tables about lined out, though. Something along the lines of (forgive me if this doesn't translate well into a forum):

<PaymentInfo>

PaymentID - primary key, integer, auto-increment, unique
MemberID - foreign key from main MemberInfo table
PurchaseID - foreign key from PurchaseType table
PurchasedAt - foreign key from PurchasedAt table
PurchaseDate - Date

​

<PurchaseType>

PurchaseID - primary key, integer, auto-increment, unique
PurchaseType - values such as 'Annual Member', 'Life Member', 'LEO Member', 'Jr Member', 'Extra Keys'
PurchaseAmount - $ value, with everything calculated off the 'Regular Member' value ($65) i.e. Life = 10xRegular, LEO = 1/2xRegular, Jr = 0.1xRegular

​

<PurchasedAt>

StoreID - primary key, integer, auto-increment, unique
StoreName - value containg name of store that sells memberships to our club in complement to their regular merchandise.
​

From that, I think I should be able to tell what kind of membership they purchased when, and since memberships normally expire at the end of the calendar year (except Life members) it should be doable to tell which years they were active (paid) members.


I'm still hashing out the table structure on this, and then get to start on the relations and forms and reports - a bit daunting for a (very) new user.

Thanks,

Monte

 

[gemma-the-husky]

the problem with names in general is that names are NOT unique.

eg - you can get several John Smiths

in general a small sample is unlikely to generate duplicates - but with a club membership where family members are involved it probably does become more likely that similarly named people will be members - so for each name you need to give them a unique identifier (probably but not necessarily the membership number) - but it is up to you when you add a new member to verify that you dont already have the member included -but as the name itself may not guarantee this, you may need to also check date of birth or some other field etc etc

 

[DCrake]

Sorry, I did not check your locality, CIU stands for Clubs and Instritue Union. In the UK there are about 2000 "working mens clubs" that traditionally were for the working man, over the years and more equality things have progressed.

 

[memilanuk]

Dave (gemma),

I completely agree... I don't think I ever insinuated (or at least I certainly didn't mean to) that I was planning on using names as even a composite natural primary key for that very reason - we *do* have several members with the same first/last names. I fully planned on using a 'MemberID' field with characteristics of integer, auto-increment, and unique - sorry I didn't make that clearer before. Very good point, though.

DCDrake,

No problem... actually I didn't have my profile information filled out yet at the time of your first post. This club is actually a sportmen's club for a shooting range set up as a non-profit organization, so we have to do a lot of things via volunteer work, donations, etc. as well as monitoring guest access and membership renewal. I'm trying to computerize a lot of things that have been done either by hand (very nearly on napkins and post-it notes) or not at all.

Actually, I'm supposed to be coming to England for about two weeks this summer for a match @ Bisley, followed by another week in Ireland.

Thanks,

Monte