Active and expiring memberships
This is my first post and I am new(ish) to the FM application, so please excuse me if I seem a noob.
I am creating a membership database for a society I belong to, and it is working pretty well. What we have is several tables including the members and payments (plus several others). Members pay their subscriptions every year, which will be reordered in the payments table.
What I want to be able to do is have something that tells me if the membership payment is up to date. This could be a field with something like "Current" or "Expired", or even a rectangle which shows red or green according to status. I suppose it is the same as an invoicing database showing if the customer account has been paid or has outstanding payments.
My thoughts so far were to get the payment date and subtract it from todays date. If the value is less than 365, then the payment is active. This works but poses me two problems though:
- We have a fixed membership year starting in November each year, so I need to have a means of automatically calculating the year in the expiring year (i.e. in this instance 31 October [year of expiry]
- Each member will have made several payments so will have (hopefully) an active membership and several expired memberships.
I can see how to do the year problem manually, but I am stuck on how to do the year automatically.
For my problem 2, I guess I would just need to filter the records in the payments table. Once I have the automatic way of working out the membership end of year, then I can have an automatic filter that shows if someone has paid within the membership year. The "status" field could then be a calculated field which returns statuses of Active or Outstanding. This links into problem 1 then, where I need to be able to calculate the date range automatically i.e. 1 November [Year] to 31 October [Year] .
Any help will be gratefully received, and my apologies if I have asked a noob question or have been unclear.