Auto calculate Membership fees

[shaneferguson]

Hi all,

Happy New Year!

I have a membership database with various layouts that contain the following fields:

Union Start

Contribution

Amount A

The Contribution field is a pop-up list containing the following contributions/membership fee list:

Adult - £1.50 per week

Juvenile - £1.11 per week

Retired - £2.50 per mth

Student - £10.00 per year

Unemployed - £2.50 per month

What i need to be able to achieve is to have the 'Amount A' field automatically work out the Contributions paid from the date in the 'Union Start' field upto the present date.

For reference:

Adult = £1.50 pw

Juvenile = £1.11 pw

Retired = £0.58 (58pence) pw

Student = £0.20 (20 pence) pw

Unemployed = £0.58 (58pence) pw

Could anyone please offer help/advice on how this can be achieved..?

I have attached a current copy of the database.

Many thanks,

Shane :-)

060106.zip

[Kent Searight]

Which table holds the field Amount A? I couldn't find it.

[sbg2]

Its a little hard to try and figure out what you are tracking with your database. As someone else mentioned there doesn't seem to be an Amount A field anywhere in the database. Also I'm not sure how you are recording a members contribution history, this does not seem to be recorded anywhere in the database? For example John Smith has a contribution type of ADULT (which I assume is supposed to be ADULT - £1.50 PER WEEK), are we to assume you want to times the number of weeks since his Union Start (1/1/2004) by £1.50? This would mean a members Contribution would never change, they would pay the same in the year 2000 as in the year 2006? Can they change types (by lets say going from an ADULT contribution to an UNEMPLOYED contribution or by going from a STUDENT to an ADULT)?

[shaneferguson]

Hi,

Thanks for replying to my post - Sorry for the delay in replying, i've been without an internet connection all weekend.... :

Kent_S : Sorry about the no Amount A being on the database - i attached the wrong copy. The correct one is attached now.

sbg2 : Yes, ideally i would like the database to update the contributions itself, based on the date the member joined the union, and the actual Contribution amount that they paying.

Also, you're very much correct - at some point the member would swap Contribution catergories, like you mentioned.

Any advice on how i could get this to work would be much appreicated.

Many thanks for your help guys.

Kindest Regards,

Shane :B

090106.zip

[sbg2]

And mightthe Contribution amount change for the type of contribution?

Ex. Adult is £1.50 PER WEEK for 2006 but in 2007 the contribution amount will be £1.55 per week.

[shaneferguson]

Hi sbg2, thanks for the reply.

Although, nothing is for certain - the likelyhood of contributions raising in the next few years (within the life of the database) is pretty low - they have only just been raised (very minor raise) in the last 6 months.

Many thanks,

Shane -)

[sbg2]

Your simple question is not nearly as simple as you might think. As you have noted a Members contribution category/type might change and somewhere down the line the amount of the contribution will go up. You should also account for the contribution Frequency being changed, ie. in 2007 someone decides student contributions will now be on a monthly basis rather than weekly. Lastly what happens in 2008 when someone decides there is no longer a need to keep two seperate categories for Retired & Unemployed instead they will now be 1 Category.

Normally when you want to track how much someone has contributed you keep track of the individual transactions. Then to figure out the total contribution made from Union Start date is fairly simple:

Create a new relationship box, named UnionContributions just for the need of a name, between the Member Contribution table and the Membership table where:

Membership::Membership Number = Member Contribution::Membership Number

and

Membership::UnionStart >= Member Contribution::TransactionDate

Then in the Membership table an Unstored Calculation field defined as:

ContributionsSinceStart = Sum(UnionContributions::Payment)

This of course leads to the question how to keep track of what fee's a member owes?

[shaneferguson]

Hi,

Many thanks for the reply.

Just to elaborate: No changes will be made to the Contributions etc, for the rest of the year (had a meeting with my boss to discuss this) - The life of the database is to be approx a year.

"...Then to figure out the total contribution made from Union Start date is fairly simple:

Create a new relationship box, named UnionContributions just for the need of a name, between the Member Contribution table and the Membership table where:

Membership::Membership Number = Member Contribution::Membership Number

and

Membership::UnionStart >= Member Contribution::TransactionDate

Then in the Membership table an Unstored Calculation field defined as:

ContributionsSinceStart = Sum(UnionContributions::Payment).... "

I'm pretty much a newbie at FM (sorry - a few fellow members of FMforums have kindly helped me out in getting the database this far) - could you please explain this/how to create this.

I appreciate your help, and for taking the time to reply.

Kindest Regards,

Shane.

[sbg2]

I have attached an example showing two types of calcualtion fields:

AmountA - which calculates how many days a member has been active, divides that by 7, rounds the number down to the next lowest number and times by their weekly fee (as defined by their Contribution Type field, see the Contribution layout. these are the numbers you quoted in your original post). This would mean someone who's Union start date was 1/1/2006 would not owe a payment until 1/8/2006. The formula would need to be tweaked to how you define when payments are due. Also a person who has a Yearly Student payment of $10 might "owe" $10.40 because there are at least 52 weeks in a year (52x.20 = 10.40), then again since the number of days in a week do not divide evenly into the number of days in a year the number might be different depending on their union start date. Also consider the number of days in a year are not consistent due to leap years.

AmountA_Sum - this field just totals up the payments a member paid which is kept track of in the Fee table. If you add a payment to the portal the members AmountA_Sum field is updated. Tracking what they owe would be a seperate issue.

Contribution.zip

[shaneferguson]

Hi sbg2,

Many thanks for the reply, file and explanation.

I'll have a look at it asap, and let you know how i get on.

Again, i appreciate your help.

Kindest Regards,

Shane. -)