Auto Calculate Field

[shaneferguson]

Hello,

I have a Membership database with a field that i would like to automate.

I have fields: Factory Start, Contribution, and Amount.

I wish to have the Amount field populated by automatically working out the total Contributions made since the Start Date.

eg: Member started on 01/01/80 - paying £1.50 per week, with today being the 05/09/06 = £xx paid in Contributions.

The contribution amounts are:

Adult £1.50 per WEEK

Juvenile £1.11 per WEEK

Retired £2.50 per MONTH

Student £10.00 per YEAR

Unemployed £2.50 per MONTH

If anyone could please help me do this, i'd be very grateful.

I have attached the database. (Use Membership Number 1 to view Member details).

Kindest Regards,

Shane.

database.zip

[CobaltSky]

I wish to have the Amount field populated by automatically working out the total Contributions made since the Start Date.

Hi Shane,

In and of itself, that's not particularly difficult. There are a number of ways you might achieve it, but one of the simplest would be to define the Amount field as an unstored calculation with a formula which subtracts the Start Date from the current system date to determine how long the person has been paying contributions, then multiplies the result by a rate value determined according to the information stored in the Contributions field.

Whilst that is the logic and it's not hard to code, you've left a number of questions unanswered that would need to be factored into a the solution to determine the exact form the calculation should take.

For instance, you haven't said when the different types of contributions fall due. Eg if a person's start date happenned to be a Tuesday, is their first payment due the following Monday at 5pm - or are all weekly payments due on the same day (in which case you'd not only need to say which day, but also to indicate how part-weeks are handled).

You've also given no indication of whether the contribution rate ever changes for a given record and if so, what should occur at that time. So for instance, if an individual is regarded as a Juvenile at their start date but then has a birthday that makes them an adult, you'd need to specify what should happen to their rate (exactly how and when it should change, if at all) and how the system should handle it. Eg if the system is supposed to be able to determine at what point the rate of contributions should change, then you'd need to set out all the rules it would need to follow.

Depending on the answers to these questions, there may be a number of other pieces of information that you'd need to provide in order for the logic of the process to hold up. So - if you're able to explain your question more fully, I'm sure there will be someone on the Forum who'll be able to suggest a solution. :