title: Using multiple inner joins with a left join
body: <p>Currently I have 3 tables that I have been able to successfully join, <code>tbl_Monthly_Chgs</code>,<code>tbl_Master_Phone</code>, and <code>tbl_inventory</code>. I'm currently Inner Joining the Master Phone Table to the Monthly Charges Table, and Left Joining the Inventory table to the Master Phone Table. This provides me an output of all the monthly charges and the device ID of those charges, including charges for that month that didn't have a device ID (There are more monthly charges in the monthly charges table than there are devices in the Inventory table). This all works perfectly, expect for the fact that both the inventory table and the monthly charges table hold IDs for many of their fields rather than actual descriptions. In order to get the descriptions for each of these IDs, I need to add more tables via joins. When I try and build this in the Table Design View in Access, I get an error messaging saying this cant be done due to ambiguous outer joins. I tried making changes to the SQL, but am having issues. Below is the last thing I tried. Is there a way to add these extra tables to the query with Inner Joins and still use the Left Join, without having to split the query up?</p>
  
  <pre><code>           SELECT tbl_Inventory.Device_Model
                          ,,tbl_Inventory_Monthly_Charges.Bill_Cycle_Date
                          ,tbl_Monthly_Charges.Client_ID
                          ,tbl_Monthly_Charges.Client_Name
                          ,tbl_Monthly_Charges.ECPD_ID
                          ,tbl_Monthly_Charges.Invoice
                          ,tbl_Monthly_Charges.Account_Number
                          ,tbl_Monthly_Charges.User_Name
                          ,tbl_Monthly_Charges.Cost_Center
                          ,tbl_Monthly_Charges.Plan_Description_Display
                          ,tbl_Monthly_Charges.Category
                          ,tbl_Monthly_Charges.Sub_Plan_Category
                          ,tbl_Monthly_Charges.Cost
                          ,tbl_Master_Phone.Wireless_Number
                          ,Max(tbl_Inventory.Bill_Cycle_Date) AS MaxOfBill_Cycle_Date
                      FROM (
                          tbl_Master_Phone INNER JOIN tbl_Monthly_Charges ON tbl_Master_Phone.Wireless_Number = tbl_Monthly_Charges.Wireless_Number
                      tbl_Master_Device INNER JOIN tbl_Inventory ON tbl_Master_Device.Device_Model = tbl_Inventory.Device_Model
                      )
                      LEFT JOIN tbl_Inventory ON tbl_Master_Phone.ID = tbl_Inventory.Wireless_Number
                      GROUP BY tbl_Inventory.Device_Model
                          ,tbl_Inventory_Monthly_Charges.Bill_Cycle_Date
                          ,tbl_Monthly_Charges.Client_ID
                          ,tbl_Monthly_Charges.Client_Name
                          ,tbl_Monthly_Charges.ECPD_ID
                          ,tbl_Monthly_Charges.Invoice
                          ,tbl_Monthly_Charges.Account_Number
                          ,tbl_Monthly_Charges.User_Name
                          ,tbl_Monthly_Charges.Cost_Center
                          ,tbl_Monthly_Charges.Plan_Description_Display
                          ,tbl_Monthly_Charges.Category
                          ,tbl_Monthly_Charges.Sub_Plan_Category
                          ,tbl_Monthly_Charges.Cost
                          ,tbl_Master_Phone.Wireless_Number;
  </code></pre>