title: MS Access - SQL LEFT JOIN multiple conditions
body: <p>I have this code which is working fine except that I need to add one more condition:</p>
  
  <pre><code>SELECT     record1.*, 
             tbl_mpsregion.maintenanceteam, 
             tbl_mpsregion.regionmps 
  INTO       tbl_sapforecast 
  FROM       tbl_mpsregion 
  RIGHT JOIN 
             ( 
                       SELECT    sap_ip19.*, 
                                 dateserial(RIGHT(trim([SAP_IP19].[PlanDate]),4),mid(trim([SAP_IP19].[PlanDate]),4,2),LEFT(trim([SAP_IP19].[PlanDate]),2)) AS [DATE/FORECAST], 
                                 tbl_labourstandard.re, 
                                 tbl_labourstandard.manning, 
                                 tbl_labourstandard.skillset AS skillset, 
                                 tbl_regionmapping.maintenanceplant, 
                                 tbl_regionmapping.area, 
                                 tbl_regionmapping.region AS region, 
                                 tbl_regionmapping.onresponse, 
                                 [RE]*[Manning]/60 AS hours 
                       FROM      (sap_ip19 
                       LEFT JOIN tbl_labourstandard 
                       ON        ( 
                                           LEFT(sap_ip19.[Task list description],3) = tbl_labourstandard.jemenawc) 
                       AND       ( 
                                           sap_ip19.[MntPlan] = cdbl(tbl_labourstandard.supplypoint ))) 
                       LEFT JOIN tbl_regionmapping 
                       ON        sap_ip19.location = cdbl([Tbl_RegionMapping].[FittersDistricts])) AS record1 
  ON         ( 
                        record1.region = [Tbl_MPSRegion].[Region]) 
  AND        ( 
                        record1.skillset = [Tbl_MPSRegion].[Skillset]) ;
  </code></pre>
  
  <p>Criteria to add is: If SAP_IP19.MntPlan does not match Tbl_LabourStandard.SupplyPoint then use 0 for Tbl_LabourStandard.SupplyPoint. I am not using Server 2000 so using CASE is not a solution. Have tried IIF and SWITCH but they are not taking query to sleep mode (not evaluating). I read that JOINS with IIF or SWITCH cannot be used. Please help!</p>