Recurring bill payments - minus previous payments balance if negative

Hoping this makes sense… this is a database for a share-house

I have a table called “Recurring bills”. For example, we have rent due every month.
I then have a table called bill payments. Tenants submit a form whenever they contribute an amount towards a bill.
The recurring bills table then calculates the total paid, to show how much is owing in total, and how much each tenant owes.
I’m trying to figure out if there is a way to make each next recurring payment, check if there is a negative balance on the previous payment, and calculate the total based on that… if that makes any sense at all.

I created a blank base with an example to show what my current set up is. As you can see, Tenant 1 has made extra payments towards rent that has his balance, and the total owing, in a negative. I’m wondering if it would be possible to then have the next month, take that negative amount and change what Tenant would be owing for that month.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.