Roll Over Students Balance from current to New Month

[adyeboah]

Hi Bytes Community,


From experience in using a property Management package, invoices were sent to Tenants last week of every month.

Receipts are captured from 21st of the current month to the 20th of the next month.

After Accounts recons, receipts capture is closed, the system updates all tenants accounts and rolls over the Closing Balances of the current month to the new month as the Opening Balance.

QUESTION:
I have a database with the following details:

1) A table -Invoice(with primary key InvoiceID) has a many to many relationship with table -Payments(with primary key PaymentID).

2) There is a Junction table -(Payment Details) which relates Invoice table and Payments table.

Invoice and receipt details of Students are entered into the junction table for the purpose of invoicing students monthly.

3) If an invoice amount of 110 Dollars is issued on 1st January 2017 to a student and payment of 90 Dollars is paid, there will be a balance of 20 Dollars on the January invoice.


How do you Roll Over a Student's Balance of 20 Dollars from the January invoice to the next invoice (February) as the Opening Balance for February in a report?

Or what Vba code is required to roll over the balance from January to February in a report?