title: How can I get a LEFT JOIN or FULL OUTER JOIN?
body: <p>I have three tables:</p>
  
  <p>Customers table:</p>
  
  <pre><code>+------------------+------------+------------+
  |     Customer     | Start Date |  End Date  |
  +------------------+------------+------------+
  | Cantrell, Beck   | 02/13/2016 | 12/22/2016 |
  | Frazier, Urielle | 11/13/2015 | 01/03/2017 |
  | Frost, Kato      | 05/27/2016 | 09/17/2016 |
  | Larsen, Kuame    | 10/27/2015 | 08/09/2016 |
  | Jennings, Echo   | 12/14/2015 | 03/09/2017 |
  +------------------+------------+------------+
  </code></pre>
  
  <p>Payments table:</p>
  
  <pre><code>+------------------+------------+------------+
  |     Customer     |    Date    |   Amount   |
  +------------------+------------+------------+
  | Frost, Kato      | 05/27/2016 | 180.00     |
  | Frost, Kato      | 06/06/2016 | 20.00      |
  | Frost, Kato      | 06/07/2016 | 40.00      |
  | Frost, Kato      | 06/13/2016 | 100.00     |
  | Frost, Kato      | 06/20/2016 | 40.00      |
  | Frost, Kato      | 06/27/2016 | 80.00      |
  | Frost, Kato      | 07/05/2016 | 60.00      |
  | Frost, Kato      | 07/12/2016 | 40.00      |
  +------------------+------------+------------+
  </code></pre>
  
  <p>I also have a third table, Dates, that is just a list of dates starting from 1/1/2010 to 1/1/2020.</p>
  
  <p>Customers are due on the day of their Start Date weekly. To get a list of charges per customer for the entirety of their project, I created the following query:</p>
  
  <pre><code>SELECT Dates.Date, 
  IIf([Dates].[date]=[Customers].[Start Date],177,77) AS Accrued
  FROM Customers, Dates
  WHERE (((Customers.[Start Date])&lt;=[date]) 
       And ((Customers.[End Date])&gt;=[date]) 
       AND ((Weekday(Dates.date))=Weekday(Customers.[Start Date])) 
       And ((Customers.Customer)=[Which Customer?]));
  </code></pre>
  
  <p>Which generates this result:</p>
  
  <pre><code>+--------------------+--------------------+
  |        Date        |      Accrued       |
  +--------------------+--------------------+
  |          5/27/2016 |                177 |
  |           6/3/2016 |                 77 |
  |          6/10/2016 |                 77 |
  |          6/17/2016 |                 77 |
  |          6/24/2016 |                 77 |
  |           7/1/2016 |                 77 |
  |           7/8/2016 |                 77 |
  |          7/15/2016 |                 77 |
  |          7/22/2016 |                 77 |
  |          7/29/2016 |                 77 |
  |           8/5/2016 |                 77 |
  |          8/12/2016 |                 77 |
  |          8/19/2016 |                 77 |
  |          8/26/2016 |                 77 |
  |           9/2/2016 |                 77 |
  |           9/9/2016 |                 77 |
  |          9/16/2016 |                 77 |
  +--------------------+--------------------+
  </code></pre>
  
  <p>Now, I need to add in all of their current payments so I added the Payments table.</p>
  
  <p>[Charges by Customer] Query:</p>
  
  <pre><code>SELECT Dates.Date, 
         IIf([Dates].[date]=[Customers].[Start Date],177,77) AS Accrued,
         Payments.Date, 
         Payments.Amount
  FROM Dates, 
       Customers INNER JOIN Payments ON Customers.Customer = Payments.Customer
  WHERE (((Customers.[Start Date])&lt;=[Dates].[date]) 
    AND ((Customers.[End Date])&gt;=[Dates].[date]) 
    AND ((Weekday([Dates].[date]))=Weekday([Customers].[Start Date])) 
    AND ((Customers.Customer)=[Which Customer?]));
  </code></pre>
  
  <p>The result of this was a list of each Accrued repeated for each payment made. I wanted to adjust the JOIN to see if I could limit to just the Accrued to just once, but every time I tried, I'd get an error stating that there was an ambiguous outer joins.</p>
  
  <p>So instead, I tried to separate it into two queries and then join them in together.</p>
  
  <p>[Payments by Customer] Query:</p>
  
  <pre><code>SELECT Payments.Date, Payments.Amount, Customers.Customer
  FROM Customers LEFT JOIN Payments ON Customers.Customer = Payments.Customer
  WHERE (((Customers.Customer)=[Which Customer?]));
  </code></pre>
  
  <p>Which produces the follow result:</p>
  
  <pre><code>+--------------------+--------------------+
  |        Date        |       Amount       |
  +--------------------+--------------------+
  |         05/27/2016 |             180.00 |
  |         06/06/2016 |              20.00 |
  |         06/07/2016 |              40.00 |
  |         06/13/2016 |             100.00 |
  |         06/20/2016 |              40.00 |
  |         06/27/2016 |              80.00 |
  |         07/05/2016 |              60.00 |
  |         07/12/2016 |              40.00 |
  +--------------------+--------------------+
  </code></pre>
  
  <p>I then tried to do a FULL OUTER JOIN, but after reading around, it doesn't seem like Access supports them. I found some examples to get around it using UNIONS.</p>
  
  <pre><code>SELECT [Charges by Customer].Date, [Charges by Customer].Accrued, [Payments by Customer].Date, [Payments by Customer].Amount
      FROM [Charges by Customer] LEFT JOIN [Payments by Customer] ON [Charges by Customer].Customer = [Payments by Customer].Customer
  UNION
  SELECT [Payments by Customer].Date, [Payments by Customer].Amount, [Payments by Customer].Date, [Payments by Customer].Amount
      FROM [Payments by Customer] LEFT JOIN [Charges by Customer] ON [Payments by Customer].Customer = [Charges by Customer].Customer
  </code></pre>
  
  <p>After fiddling with this for awhile, I'm rather stuck and not sure where to proceed. I'm kinda lost with Access. I'm much more familiar with MySQL and the differences (and UI) is making it difficult for me to wrap my head around what I need to do to get my desired result.</p>
  
  <p>My ideal result would be something like this:</p>
  
  <pre><code>+-----------+---------+------------+--------+
  | Date      | Accrued |    Date    | Amount |
  +-----------+---------+------------+--------+
  | 5/27/2016 |     177 | 05/27/2016 | 180.00 |
  |  6/3/2016 |      77 |            |        |
  |           |         | 06/06/2016 |  20.00 |
  |           |         | 06/07/2016 |  40.00 |
  | 6/10/2016 |      77 |            |        |
  |           |         | 06/13/2016 | 100.00 |
  | 6/17/2016 |      77 |            |        |
  |           |         | 06/20/2016 |  40.00 |
  | 6/24/2016 |      77 |            |        |
  |           |         | 06/27/2016 |  80.00 |
  |  7/1/2016 |      77 |            |        |
  |           |         | 07/05/2016 |  60.00 |
  |  7/8/2016 |      77 |            |        |
  |           |         | 07/12/2016 |  40.00 |
  | 7/15/2016 |      77 |            |        |
  | 7/22/2016 |      77 |            |        |
  | 7/29/2016 |      77 |            |        |
  |  8/5/2016 |      77 |            |        |
  | 8/12/2016 |      77 |            |        |
  | 8/19/2016 |      77 |            |        |
  | 8/26/2016 |      77 |            |        |
  |  9/2/2016 |      77 |            |        |
  |  9/9/2016 |      77 |            |        |
  | 9/16/2016 |      77 |            |        |
  +-----------+---------+------------+--------+
  </code></pre>
  
  <p>If you have any ideas or solutions, I'd be very grateful. Thanks!</p>