Computing membership expiration dates
Posted: Thu Sep 13, 2018 6:59 am
Hello community. My first post here. I'm a newbie in Base.
I'm setting up a database to keep track of membership and payments. I created different tables with member information, using the 3 rules of normality, and created several nifty forms to fill my tables with actual data. My problem is the computation of expiration date of membership for each member. I'm trying to set this as a Query. I don't want to involve macros, if possible.
The membership could be purchased at any point of the year, and expires on Dec 31 (rules of the society). There are packages of 1 year and 3 years. So, a member could buy a 3 year membership on January 2015 (expiring on Dec 2017), and then another year membership on February 2015 (expiring now on Dec 2018), plus another year at some point of 2016, pushing the expiration to Dec 2019. I can compute an expiration date per each payment, but that is meaningless. I need some form of aggregation that's smart eough to undertand that a Payment is being done when the membership is active, and sum the purchased time to the current expiration date, and that needs to be done per each member.
I'm not handling tens of thousands of members, so using a complex convoluted approach is feasible without fearing a high impact on performance (I think).
Here's a small example
I can produce a view/query with only the membership payments
I managed to add, via joins and COUNT() along with a <= condition, a payment counter per member. Not sure if that's useful.
In any case, I need to eventually compute this result. The status is computed based on the current date (September 2018).
Any help or advice is appreciated
I'm setting up a database to keep track of membership and payments. I created different tables with member information, using the 3 rules of normality, and created several nifty forms to fill my tables with actual data. My problem is the computation of expiration date of membership for each member. I'm trying to set this as a Query. I don't want to involve macros, if possible.
The membership could be purchased at any point of the year, and expires on Dec 31 (rules of the society). There are packages of 1 year and 3 years. So, a member could buy a 3 year membership on January 2015 (expiring on Dec 2017), and then another year membership on February 2015 (expiring now on Dec 2018), plus another year at some point of 2016, pushing the expiration to Dec 2019. I can compute an expiration date per each payment, but that is meaningless. I need some form of aggregation that's smart eough to undertand that a Payment is being done when the membership is active, and sum the purchased time to the current expiration date, and that needs to be done per each member.
I'm not handling tens of thousands of members, so using a complex convoluted approach is feasible without fearing a high impact on performance (I think).
Here's a small example
I can produce a view/query with only the membership payments
Code: Select all
memberID paymentID paymentDate expirePeriod
0 0 2012-02-02 1
0 10 2013-02-15 3
0 30 2014-01-10 1
0 60 2015-08-15 3
1 5 2012-03-12 1
1 15 2013-04-01 1
1 20 2013-10-01 3
1 35 2014-02-01 1
Code: Select all
count memberID paymentID paymentDate expirePeriod
1 0 0 2012-02-02 1
2 0 10 2013-02-15 3
3 0 30 2014-01-10 1
4 0 60 2015-08-15 3
1 1 5 2012-03-12 1
2 1 15 2013-04-01 1
3 1 20 2013-10-01 3
4 1 35 2014-02-01 1
Code: Select all
memberID expirationDate memberStatus
0 2019-12-31 active
1 2017-12-31 inactive