Thread: Help with Membership Formula (Access 2003 SP2)

1. Help with Membership Formula (Access 2003 SP2)

I am trying to work out a formula for membership renewal fee with the following parameters:
(A) Companies with over 12 months pay full fee per member;
([img]/forums/images/smilies/cool.gif[/img] A higher number of members allows a lower rate per member eg 1 member = \$500/yr, 2 members = \$980/yr, 3 = \$1440/yr, etc, 6 = \$2700/yr and so on;
© Companies with memberships less than 12 months pay pro-rata fee per member based on point B.

The problem is where a company has a combination of several members longer than 12 months and others less than 12 months. For example, if XYZ has 3 members with greater than 12 months membership, plus another 3 with only 10 months membership, how much is the company charged? Based on this scenario, it would be \$2640 (\$1440+\$1200). If there were six long term members, the fee is \$2700. After that, it seems to be a disadvantage to have any members for less than 12 months in addition to members longer than 12 months. Eight long term members pay \$3440 altogether whereas 4 long term plus 4 ten month members pay \$3447!

Has anyone come across this before? Any ideas on how to construct a formula?

Thanks
Nick

2. Re: Help with Membership Formula (Access 2003 SP2)

Do you really want to calculate the rate for each group separately? Another way would be:

Company has 8 members, so yearly rate per member is \$500 - 7 * \$10 = \$430.
If these, 4 are long term members, and 4 have been a member for 10 months, so total fee is 4 * \$430 + 4 * (10/12) * \$430 = \$3153.33

Does that sound reasonable?

3. Re: Help with Membership Formula (Access 2003 SP2)

I suppose Felton should answer this, but from my reading of his post the fee for 8 long term members would be:

500 + 490 + 480 + 470 + 460 + 450 + 440 + 430 = 3720

This is an Arithmetic Progression with a first term of 500 and a Common Difference of -10 and 8 terms , so the total fee for n long term members is:

(n/2) * (2 * 500 - (n-1)* 10) (n/2)* (2a +(n-1)d)

4. Re: Help with Membership Formula (Access 2003 SP2)

I read it differently, so I agree that Felton will have to tell which version is correct.

5. Re: Help with Membership Formula (Access 2003 SP2)

Thanks for your prompt and thoughtful responses. Sorry for the ambiguity but nevertheless, your answers got me thinking.

If the company only had 8 long term members, the fee would be \$3440. If there were only 8 short term eg 10 month members, the fee would be 3440*10/12, that is, \$2867.

However, it was the combination of both long and short that messed with my brain. So, I restarted my calculations considering 4 long term and 4 ten month members still equates to 8 members altogether.

If I take the full 8 member fee of \$3440 and proportion it to the 4 full time members, I get \$1720. Then I take the same full 8 member fee and proportion it to the 4 ten month members on a pro-rata rate, I get \$1433.33 ie 3440*(4/8)*(10/12).

The total of those two portions gives me \$3153.33 which works out to be 92% of the original full 8 member fee of \$3440. That answer seems workable.

Thanks again for your time and being a sounding board.

Cheers

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•