CODEWITHSUNDEEP
ms-accessouter-join
Christopher Jack
Christopher Jack

Reputation: 97

Multi Join in Access not working

I have a few tables that I am connecting to. However it is now throwing up an error message -

JOIN expression not supported.

I think it has to do with the number of brackets but I have double checked and it seems to be OK.

Select
T1.ASSR_FEE as AssessorFee,
T1.CAU_DT as Comment,
T1.Incident_Date as IncidentDate,
T1.Inv_amount as InvoiceAmount,
T1.Rep_Cost as RepCost,
T1.cl_id_ecm as ReferenceId,
t1.Est_cost_init as EstimatedCost,
t6.name as Location,
T2.CO_Name & ' ' & T2.Surname as DriverName,
T3.CO_Name & ' ' & T3.Surname as InspectorName,
T7.Full_Desc as Customer,
T8.Full_Desc as CollectionPoint,
T9.Full_Desc as MainDescription,
T10.Full_Desc as Subdescription  
FROM
    ((((((((cl_details T1)
    LEFT OUTER JOIN [C:\Users\cjack\Desktop\Test Stuff\ecm_hol.mdb].tb_pers T2 on T1.pers_no = T2.ref_id)
    LEFT OUTER JOIN [C:\Users\cjack\Desktop\Test Stuff\ecm_hol.mdb].tb_pers T3 on T1.insp_id = T3.ref_id)
    LEFT OUTER JOIN cl_coding T4 on T1.cl_id = T4.cl_id)
    Left outer join Dealers T6 on T1.cldeta_dealer0 = T6.dealer0)
    Left Outer Join Sy_code_list T7 on t4.ID_Code = t7.ID_Code and t4.Level_2 = t7.Level_2 and t7.ID_Code = 2 and (t7.Level_3 = 0 or t7.Level_3 = -1))
    Left Outer Join Sy_code_list T8 on t4.ID_Code = t8.ID_Code and t4.Level_2 = t8.Level_2 and t8.ID_Code = 11 and (t8.Level_3 = 0 or t8.Level_3 = -1))
    Left Outer Join Sy_code_list T9 on t4.ID_Code = t9.ID_Code and t4.Level_2  = t9.Level_2 and t9.ID_Code = 5 and (t9.Level_3 = 0 or t9.Level_3 = -1))
    Left Outer Join Sy_code_list T10 on t4.ID_Code = t10.ID_Code and t4.Level_2 = t10.Level_2 and t4.Level_3 = t10.Level_3 and t10.ID_Code = 5 and (t10.Level_4 = 0 or t10.Level_4 = -1)

Any help appreciated.

Thanks

Upvotes: 0

Views: 19

Answers (1)

Christopher Jack
Christopher Jack

Reputation: 97

Have to add Brackets

Select
T1.ASSR_FEE as AssessorFee,
T1.CAU_DT as Comment,
T1.Incident_Date as IncidentDate,
T1.Inv_amount as InvoiceAmount,
T1.Rep_Cost as RepCost,
T1.cl_id_ecm as ReferenceId,
t1.Est_cost_init as EstimatedCost,
t6.name as Location,
T2.CO_Name & ' ' & T2.Surname as DriverName,
T3.CO_Name & ' ' & T3.Surname as InspectorName,
T7.Full_Desc as Customer,
T8.Full_Desc as CollectionPoint,
T9.Full_Desc as MainDescription,
T10.Full_Desc as Subdescription  
FROM
((((((((cl_details T1)
LEFT OUTER JOIN [C:\Users\cjack\Desktop\Test Stuff\ecm_hol.mdb].tb_pers T2 on (T1.pers_no = T2.ref_id))
LEFT OUTER JOIN [C:\Users\cjack\Desktop\Test Stuff\ecm_hol.mdb].tb_pers T3 on (T1.insp_id = T3.ref_id))
LEFT OUTER JOIN cl_coding T4 on (T1.cl_id = T4.cl_id))
Left outer join Dealers T6 on (T1.cldeta_dealer0 = T6.dealer0))
Left Outer Join Sy_code_list T7 on (t4.ID_Code = t7.ID_Code and t4.Level_2 = t7.Level_2 and t7.ID_Code = 2 and (t7.Level_3 = 0 or t7.Level_3 = -1)))
Left Outer Join Sy_code_list T8 on (t4.ID_Code = t8.ID_Code and t4.Level_2 = t8.Level_2 and t8.ID_Code = 11 and (t8.Level_3 = 0 or t8.Level_3 = -1)))
Left Outer Join Sy_code_list T9 on (t4.ID_Code = t9.ID_Code and t4.Level_2  = t9.Level_2 and t9.ID_Code = 5 and (t9.Level_3 = 0 or t9.Level_3 = -1)))
Left Outer Join Sy_code_list T10 on (t4.ID_Code = t10.ID_Code and t4.Level_2 = t10.Level_2 and t4.Level_3 = t10.Level_3 and t10.ID_Code = 5 and (t10.Level_4 = 0 or t10.Level_4 = -1))

Upvotes: 1

Related Questions