I have two tables in a MS Access DB. One for Tentants, one for payment transactions.
I would like to know how I can pull all tenants who have not paid rent for a specific month.
In the transaction table (tPayments) is a field called "PaymentMonth". This field is text and is in the format: "FEB2004". There is another field called: TenantID which relates to the tTentants Table.
This may be easy, but I'm new at using LEFT JOIN, RIGHT JOIN and possibly nested SELECTs.
I can get a result, but my results only show tenants who have not paid for FEB2004 listed in the tPayments Table. What I was in all tenants from the tTenant Table who has no payment in the tPayments Table.
That worked. I thought of doing the inside SELECT (nested) but I didn't know how it was done. Also, I wasn't aware you could use SELECT 1 to select one record... I was trying to use DISTINCT.. to no success.