Date Calculation

[cbouse]

Hi all, I searched around but couldn't really find a calculation that helped.

I have a database for membership to a shooting range. There are three date fields I need to work with, (date_first_joined, date_renewed, date_membership_expired).

when a new record is created the expiration date needs to be the date joined +365 days, simple. when i enter a date into the date renewed field, I need the expiration date to look at that date and calculate a year past the date renewed and add the remaining days in the year to it to give me a new expiration date.

Been trying for a while but just cant get it.

Thank you in advance for the help.....

[LaRetta]

Date ( Month ( date_renewed ) ; Day ( date_renewed ) ; Year ( date_renewed ) + 1 )

UPDATE: I responded a bit hastily ... each membership period should be a new record, showing the amount paid for that yearly membership etc. If you just always change the date_renewed, you will lose that audit track.

I'm unsure if I answered your question or not ... if not, please tell us what doesn't work or provide an example of what you want the result to be and we'll work through it with you.

LaRetta :wink2:

Edited January 11, 2009 by Guest
Added update

[LaRetta]

OH MY GOODNESS! And cbouse, welcome to FM Forums!!! :laugh2:

[comment]

I believe it would be easier (and better for many reasons) to have a separate table for subscriptions. Then it's just a matter of adding the sum of subscription periods to the date_joined in the Member record.

[cbouse]

Thank you for the quick post. The hard part i need on this calculation is the pro rated part.

so if i have a membership that was created on 01/01/09 it expires on 01/01/10, easy

but if my member decides to renew on 11/01/09 and not 12/31/09, i need the expired date to take the remaining days of that persons membership and add them to the new renewal term.

Thanks again for the quick reply and welcome.... :

[comment]

I responded a bit hastily ...

No wonder I am repeating what you said, then.

[LaRetta]

I'll let Michael run with this one ... we're on same wavelength but he is clearer in his responses (and quite a bit better as well). I will add this: You can prorate a contract to the day and bring all your renewals due same time (many apartment complexes use this method). It is very wise because it makes tracking renewals a once-a-year issue).

[cbouse]

I was just thinking how i kinda need to elaborate on my problem.

So i come in and sign up for a membership and that populates the date first joined. That needs to automatically put a date in the membership expiration field which is 365 days past the date in date first joined field. When my membership expires ill come in and renew, hence the date renewed field. That needs add 365 days to the membership expiration field giving me a new year.

The big problem is what if i come in and pay my membership before my account expires, (i.e. im out of the country and want to pay a month or two in advance to renew my membership. So now i have to find out how many days are remaining in the year and add to the 365 days.

And we just started thinking about leap years and stuff....

Im a novice just trying to help a non-profit group and this is way over my head...

Thanks again for taking the time to help

[comment]

See if this sketch helps.

Members.fp7.zip

[LaRetta]

I wouldn't keep the Join Date in the Members table. Why? If they let their renewal expire, how to you account for the gap? Let's say it expires and they come back to renew past their Expiration, around 1/20/2008, how would you handle it? If you change their Join Date, they have suddenly paid for 4 more years.

I was also considering exceptions - some people might want to pay for 6-months (there are ALWAYS unusual situations, ie, the Owners cousin etc). Some people might always want part of their money back because they aren't satisfield (okay, okay, I get too detailed, but these things should be considered at the onset).

The Join Date might be kept in Member as historical information but I suggest that the Payment table have a StartDate. Just thinking out loud ...

Edited January 12, 2009 by Guest
Corrected typo

[comment]

Those are good points, and also the reasons why I called it a sketch.

[cbouse]

Sorry it took so long for a reply, work is kicking my butt...

You brought up some good points, like i said, still a novice at this so its hard for me to see all the angels.

The date first joined will never change. Its more of a historical date to keep track of how long someone has been a member. Its only used for the first year of a membership and that's really it. After that, the date renewed is the only field altered after that and the calculation for the expiration date field comes from that date.

Keeping a record of renewal date and amount is exactly what i am about to start working on. Looks like you already got me started.

I REALLY appreciate the help with this. If you have any further guidance / suggestions, PLEASE, let me know...

Thanks again,

Chris