Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: Finding out who has not paid

    HI...
    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.

    TIA,
    Erik

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select tName
    from tTenants
    where not exists
    ( select 1 from tPayments
    where TenantID = tTenants.ID
    and PaymentMonth = 'FEB2004' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    7
    Thank you much!

    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.

    Thanks again!
    Erik

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •