Monthly payments and monthly tuition solution
I have put together a database for tracking monthly tuition (which changes) and payments received (sometime more than one). Two questions:
A) If enrollment is set-up with M, T, W, TH, F as text field and I've use the following calculation to populate the EnrolledDays calculation field [If (Monday=Enrolled;1;0) + If (Tuesday=Enrolled;1;0) + If (Wednesday=Enrolled;1;0)+If (Thursday=Enrolled;1;0)+If (Friday=Enrolled;1;0)] Would it be best to make duplicates of these fields for each month so I can have a record before October's changes are made? Is there a better way to do this?
B) Tuition is calculated based on the above EnrolledDays calculation multiplied by the daily rate and payments are received during the month to be applied against the tuition. I have made an invoice table that holds monthly invoices and separate payments table related to the invoices and Family accounts via kf_familyID field. I would like any suggestions on how best to sum payments related to each family, enter it into the September_payments field in the invoices table, then calculate the October tuition without having the September tuition change (not sure that it will change but worried that it may - September tuition is a calculated field)