You can probably set up a self join that refers to the current students record but one month previous.
I am assuming one record per student per month and that two fields already exist in your table:
StudentID (Unique Serial Number)
TransDate (Date of record's entry)
TansDate should be set to auto-enter a date with a consistant "day" value.
One such option: Date ( Month (self) ; 1 ; Year (self) ) with the "do not replace existing value" option cleared. (This assumes you enter the date manually each time.)
Define a new field, cPrevMonth:
Date ( Month ( TransDate ) - 1 ; Day ( TransDate ) ; Year ( TransDate ) )
Create a new table occurrence, PreviousMonth for your student table. and define your relationship
StudentTable :: cPrevMonth = PreviousMonth :: TransDate AND
StudentTable :: StudentID = PreviousMonth :: StudentID
Now you can refer to the fields in the PreviousMonth table Occurrence in any calculations or simply add the balance field from PreviousMonth on your layout.
Phil, you are absolutely great!
I will try this right away. Thank you very much for sharing your time and mind. Very cool of you.
I'm confused...is this a self-join?
I made the new table, but I cannot get it to show previous balance.
During a month, any student can pay more than once. AND the payment info comes from another table that collects the finances. These are from the bank and the transaction date is always moving. So, maybe this is why I cannot get it to work?
Could there be another way to relate these tables that can show the last month's balance?
Thanks again for your time and knowledge and most of all, your effort and willingness to be so helpful.
"I'm confused...is this a self-join?"
Yes, I described a self join. If you have a calculation field that extracts the current month's total payment, then you can use the self join to refer to last month's total payment.
To extract the total payment from your payment table, do the following:
Define a cMonth field: Date (Month (transactiondatefield) ; 1 ; year (transactiondatefield) )
Now all payments in a given month will show the same date in cMonth.
You can use this relationship for the current month's total payment:
StudentTable :: TransDate = paymenttable :: cMonth AND
StudentTable :: StudentID = paymenttable :: StudentID
and this relationship for the previous month's total payment:
StudentTable :: cPrevMonth = PaymentTable :: cMonth AND
StudentTable :: StudentID = PaymentTable :: StudentID
Thank you for the answer on this one. I was stuck trying to get previous record values to show up in new records, and this was the answer. It took some playing around with the relationship diagram, but I got it. Thanks again.