Relationship in my database are set-up as follows if this helps.
A) If the data changes with each month, then the best approach is to have a table of related records where you create a new record for each month's enrollment. Your calculations for the next month's tuition can then be based on the most recently created related record (A date field in each table can be part of the "match" fields defined in the relationship.)
B) You need to deal with A) before you tackle this issue, but a full up payment tracking system has to handle:
- Partial payments that do not completely pay off a given bill. (Sometimes this is due to a mistake on the part of the client.)
- Payments that pay off more than one bill.
- Payments that exceed the amount owed. (This could be a client error or they could be "paying in advance".)
This describes a many to many relationship between payments and bills. As a given payment may need to be linked to more than one bill and a given bill may need to be linked to more than one payment.
Start with these relationships:
Payments::__pkPaymentID = Payment_Invoice::_fkPaymentID
Invoices::__pkInvoiceD = Payment_Invoice::_fkInvoiceD
You can place a portal to Payment_Invoice on the Payments layout to list and select an Invoice record for each given Payments record. Fields from Invoices can be included in the Portal to show additional info about each selected Invoice record and the _fkInvoiceD field can be set up with a value list for selecting Invoice records by their ID field. And an Amount field in Payment_Invoice can record that portion of the total payment received that applies to a specific Invoice record.
Thanks for the response Phil.
I'm unclear if you mean create a new table (EnrollmentRecords) relate it to the Family_Student table via kf_studentID. If this is correct, I need to add a autocreation date field to the new table. How do I include this in the relationship information so that I only find the enrollment record for October and not the record for September when I do my calculations for tuition? Still new at this.
A date field can certainly be set to auto-enter the creation date or a script can create the record and assign a date at the same time. I would use a date for the first day of the specified month for this and a script may be a better option if it's possible that you might create this record both before or after the current date is from the new month.
Presumably each invoice is specific to a particular month and year so a date field for the first day of the specified month in invoices can match to the same type of date field in this related table. This then would use two pairs of match fields, one that matches by student or family ID and one that matches by this date.
And here is a useful calculation for computing the date for the first day of the month for a date entered into a date field:
Datefield - Day ( DateField ) + 1 //be sure to select Date as the result type