MS Access - SQL LEFT JOIN multiple conditions

Question

I have this code which is working fine except that I need to add one more condition:

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]) ;

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!

Are you certain? [http://www.techonthenet.com/access/functions/advanced/case.php](http://www.techonthenet.com/access/functions/advanced/case.php) Are you using a very old version of access, or a connector that doesn't handle CASE, or maybe the syntax you tried isn't quite right...? EDIT: Nevermind, I see that it's available in VBA but not the standard SQL. I guess that could point you to an alternative. Also check out [http://stackoverflow.com/questions/247858/coalesce-alternative-in-access-sql](Nz). — GumbyG, May 18 '16 at 13:30
Sorry about that - ran out of edit time. Check out [Nz](http://stackoverflow.com/questions/247858/coalesce-alternative-in-access-sql) — GumbyG, May 18 '16 at 13:35
Thanks GumbyG! I'll look at different examples. I was trying to use these functions in condition of join which may be an incorrect way. — Zia, May 18 '16 at 14:05
I see what you're trying to do now, and I suspect Nz will not help. I would try to move to a UNION of two query results where tbl_labourstandard is inner joined in both - one with the linkage to sap_ip19.[MntPlan], and one with a condition of zero for Tbl_LabourStandard.SupplyPoint . Is that feasable? — GumbyG, May 18 '16 at 14:37

score 1 · Answer 1 · answered May 18 '16 at 13:35

1

You should be able to add if's or switches but you could always handle this with an OR - it's not the most performance friendly but it should get the job done, example below:

LEFT JOIN tbl_labourstandard
ON 
(LEFT(sap_ip19.[Task list description],3) = tbl_labourstandard.jemenawc) 
AND 
((Tbl_LabourStandard.SupplyPoint = SAP_IP19.MntPlan AND
sap_ip19.[MntPlan] = cdbl(tbl_labourstandard.supplypoint)) 
OR (sap_ip19.[MntPlan] = 0))

answered May 18 '16 at 13:35

Chris

915
8
28

Thanks Chris! This will not change the recordset. Table LabourStandard has a list of generic work time with supplypoint as 0 and specific work time with supplypoint as unique number. SAP_IP19.MntPlan would only have unique numbers in it. Of course they repeat and that is why I am using composite with WC. – Zia May 18 '16 at 13:42
From your original --- 'If SAP_IP19.MntPlan does not match Tbl_LabourStandard.SupplyPoint then use 0 for Tbl_LabourStandard.SupplyPoint' --- this is why I added a 0, what is actually required ? – Chris May 18 '16 at 13:51
First of all MntPlan sitting in SAP_IP19 would not have 0 LabourStandard.SupplyPoint would have 0. If we place 0 in SupplyPoint within this condition, it will duplicate a line for where MntPlan was found in the record. If I am not wrong. – Zia May 18 '16 at 13:56
What line is this - 'duplicate a line for where MntPlan was found in the record.' - if you don't want mntPlan to equal 0 then by cutting the OR you can just join when they match. Without knowledge of your table structure or what you are trying to achieve it is hard to help. – Chris May 18 '16 at 14:02
PlanDate Type Plnt Room Location Mn.wk.ctr MntPlan Cal. week Call Status Created Created on Loc/AccAssmt Strat. MaintPlan dscrptn Task list description 06.06.2016 GM60 G407 10202 320 GSDXXXXX 80014864 2016/23 Further dates (simulation) CONV02 26.05.2015 7329460 P10 PIPELINE PATROL WEEKLY TRUNK & PRIM P10 PIPELINE PATROL WEEKLY TRUNK & PRIM – Zia May 18 '16 at 14:11
Region Skillset MaintenanceTeam RegionMPS Country Central West SPR/LMP Distribution Country Central West Country Central West BSC Distribution Country Central West – Zia May 18 '16 at 14:12
JemenaWC SupplyPoint Description Manning UseTojAsReForActuals RE SkillSet 105 0 Escape - Meter (if Meter changed use 542) 1 0 30 R&M 106 0 Escape - Outlet Meter Connection 1 0 26 R&M – Zia May 18 '16 at 14:13
I don't know how else to add table structure here with 1-2 rows as example. First table is IP19, second is RegionMapping and third is LabourStandard – Zia May 18 '16 at 14:14
1

... the above means absolutely nothing without context. You could modify your question, and really focus on what you require and what your problem is, providing the table structure etc there. Alternatively have a read of some of the stack overflow guides on question asking i recommend the following: http://stackoverflow.com/help/mcve and http://stackoverflow.com/help/how-to-ask and maybe you will be able to get better help with a better more structure question / problem. – Chris May 18 '16 at 14:23

MS Access - SQL LEFT JOIN multiple conditions

1 Answers1