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