title: Access left join not working as I am picturing it
body: <p>I am trying to write a query that involves 3 tables and left joining two of them onto a main one.</p>
  
  <pre><code>SELECT UNIT_MAIN.UNIT_NO, DEPT_MAIN.LEV_2, Card.CardNumberLong AS [Some
  Number], Card.Enabled, F_CARD.CARD_NO, F_CARD.END_DT
  FROM (((UNIT_MAIN 
  INNER JOIN DEPT_MAIN ON UNIT_MAIN.USING_DEPT = DEPT_MAIN.DEPT_ID) 
  LEFT JOIN Card ON (UNIT_MAIN.UNIT_NO = Card.UnitCode AND Card.Enabled = True) )
  LEFT JOIN F_CARD ON (UNIT_MAIN.UNIT_ID = F_CARD.ASSIGNED_ID AND (F_CARD.END_DT) Is Null  ))
  WHERE  (((UNIT_MAIN.STATUS)="A") AND ((DEPT_MAIN.LEV_2)="AM") AND ((Card.Enabled)=True) ) 
  OR (((UNIT_MAIN.STATUS)="D") AND ((DEPT_MAIN.LEV_2)="AM") AND ((Card.Enabled)=True) ) 
  </code></pre>
  
  <p>The issue I am having is when F_CARD table has rows where the F_CARD.END_DT is not null, causing the main table (unit table) not to show up even though it is a left join and the F_CARD table rows did not satisfy the join condition (or I am to believe).</p>
  
  <p>I don't have any where clauses on the F_CARD table and they are only on the join condition.</p>
  
  <p><em>edit</em>
  When I perform </p>
  
  <pre><code>LEFT JOIN MFIVE_F_CARD ON (MFIVE_UNIT_DEPT_COMP_MAIN.UNIT_ID = MFIVE_F_CARD.ASSIGNED_ID AND ((MFIVE_F_CARD.END_DT) Is Null)
  </code></pre>
  
  <p>The unit does not appear if the F_CARD table contained rows that had an END_DT, I was to believe that since the left join condition failed, the inner table (unit table) should appear regardless.</p>
  
  <p>If I remove any F_CARD related values from the query, the missing units I am looking for appear. It is an inner join, left join, left join. When the second left join happens, I lose rows even when they should appear.</p>
  
  <p>I narrowed down my joins and just did the inner with the left with F_CARD. Trying to see why it doesnt return rows where the join fails.</p>
  
  <p>Tried to do the following, however im getting an unsupported join error...</p>
  
  <pre><code>SELECT 
      UNIT_MAIN.UNIT_NO
      ,DEPT_MAIN.LEV_2
      ,Card.CardNumberLong AS [SomeNumber]
      ,Card.Enabled
      ,F_CARD.CARD_NO
      ,F_CARD.END_DT FROM 
      (
          (
              (
                  UNIT_MAIN 
                  INNER JOIN DEPT_MAIN
                  ON UNIT_MAIN.USING_DEPT = DEPT_MAIN.DEPT_ID
              ) 
              LEFT JOIN Card
              ON  (UNIT_MAIN.UNIT_NO = Card.UnitCode AND Card.Enabled = True)
          ) 
          LEFT JOIN F_CARD
          ON (UNIT_MAIN.UNIT_ID = F_CARD.ASSIGNED_ID AND F_CARD.END_DT Is Null)
      ) WHERE 
      (UNIT_MAIN.STATUS = "A" OR UNIT_MAIN.STATUS = "D")
      AND DEPT_MAIN.LEV_2 = "AM"
  </code></pre>
  
  <p>Thanks.</p>