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?