I'm building a rental database to track tenant payments, and I'd like to create a report that simply says how much money, if any, is owed when the report is run.



The tables:

Tenants
- Tenant ID
- Tenant Name
- Tenant Rent (expected rent per month)
I haven't added a move-in date but I've been thinking about doing that.

Transactions
- Transaction ID (primary key)
- Tenant ID (foreign key, relationship with Tenants->Tenant ID)
- Transaction Amount
- Transaction Date
- Some other fields that will be used for other reports, un-important here.

Without a move-in date, I'd simply like to calculate the total amount of rent paid since the db started tracking payments, and subtract that sum from the calculated amount of rent due since the db started tracking payments.

I was easily able to build a query that returns total payments by all tenants, but how to build a query or report that determines how much rent is due (since the db started keeping track) is eluding me.

Somehow I need to identify the month of the earliest existing payment, declare that as the start month, and then multiply the number of all elapsed months by the rent rate for each tenant, and display the difference between total due and total paid.

I'm not even sure if this is the right sub-forum to post this in, but it seems like a combination of report and query is what is going to be required for this, no?