Hello,
I have two tables, one with the details of the tenants and one with the payments received (bank statement where tenants pay). Payments from the tenants can be rent or other payments.
How can I create a colum to see if someone has payed the rent and for which month?
The tables have several columns but these are the important ones:
Table 1
Tenant ID | Name | Rent Amount | |
6546 | Doe | 350 | |
2347 | Poll | 500 | |
2345 | Potter | 400 | |
Table 2
Date | Payer | Payment Reason | Amount | | | Tenant ID |
10/8/2022 | Potter | Rent Potter | 400 | | | 2345 |
10/1/2022 | Doe | Rent + Deposit | 500 | | | 6546 |
10/1/2022 | Citybank | Rent Poll | 500 | | | 2347 |
10/1/2022 | Citybank | Deposit Poll | 100 | | | 2347 |
Column "Tentant ID", I created (in Table 2) to be able to relate the two tables.
This is the column I need:
Monthly payment received: has a tenant pay his rent? we need to check in either column "Payer" or "Payment Reason" if "Tenant" has made a payment. Unfortunately both columns have to be checked as tenants sometimes pays directly with the bank account and sometimes go to the bank an pay, so their name can appear in either column. We also need to checked his actual due monthly payment (in Table 1) and when the payment was made (month).
Tenant ID | Name | Rent Amount | Monthly payment | Overall payment received | Other payments |
6546 | Doe | 350 | October | 500 | Deposit |
2347 | Poll | 500 | October | 600 | Deposit |
2345 | Potter | 400 | October | 400 | None |
I made already column Overall Payment received but I do not know how to combine the date. From the overall payment column one can deduce what was the payment for.
I would appreciate your help.
