I have a database which tracks both rental payments and expenses. There are three tables: tenants, payments, and expenses. I'd like a single report to contain a summary of total annual payments collected by room number, and a summary of expenses by category. I can easily create a report containing total rent payments by tenant, but how can I get both sets of data in one layout, which can also be exported to Excel?
I have attached a quick draft of the solution.
Why not put Payments and Expenses into one table? It's money coming or going out, related to one tenant/room.
Or use a virtual list technique; have a look into your modified sample and study script #4.