Aggregating payments against amount owing


#1

I have a vacation rental database. In the Bookings table each booking has a unique BookingRef and a RentalAmount (e.g. $2000).

Payments against the total amount are made in stages, tracked in a Payments table that ties each transaction to the unique BookingRef. For example (against the $2000 RentalAmount):

1/1/18 | Deposit | $500
1/7/18 | Stage Payment | $750
1/10/18 | Stage Payment | $750

I want to be able to produce Statements that list the Payments by date for each BookingRef and shows the outstanding balance after each transaction. For example:

1/1/18 | Deposit | $500 | $1500
1/7/18 | Stage Payment | $750 | $750
1/10/18 | Stage Payment | $750 | $0

I can’t find how to do this! I’ve looked through here and Support. I’ve tried the Pivot Table Block but it’s too simplistic.

Can it be done?