Results 1 to 5 of 5

20061025, 09:50 #1
 Join Date
 Nov 2001
 Location
 Sydney, New South Wales, Australia
 Posts
 216
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 prorata 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

20061025, 11:22 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
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?

20061025, 21:47 #3
 Join Date
 Jun 2002
 Location
 Mt Macedon, Victoria, Australia
 Posts
 3,993
 Thanks
 1
 Thanked 45 Times in 44 Posts
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  (n1)* 10) (n/2)* (2a +(n1)d)Regards
John

20061025, 21:59 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
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.

20061026, 00:04 #5
 Join Date
 Nov 2001
 Location
 Sydney, New South Wales, Australia
 Posts
 216
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 prorata 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