Error when trying to connect to ODBC to MS Access databse

[Anonymous]

Hi,

I am using PowerBI with an ODBC data connection.

When I run my SQL

Select    T1.ASSR_FEE,    T1.CAU_DT,    T1.Incident_Date,    T1.Inv_amount,    T1.Rep_Cost,    T2.Co_Name + " " + T2.Surname as DriverName,    T3.Co_Name + " " + T3.Surname as InspectorNamefrom    cl_details as T1
    Left Join tb_pers as T2 on T1.pers_no = T2.ref_id
    left join tb_pers as T3 on T1.insp_id = T3.ref_id

I get the error message........

Details: "ODBC: ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'T1.pers_no = T2.ref_id
    left join tb_pers as T3 on T1.insp_id = T3.ref_i'."

I have check the syntax online and it seem to be correct... any help appreciated.

[quentin_vigne]

Hi @Anonymous

You forgot the "FROM" 

Quentin

[Anonymous]

Nope its got the form in it, for somereason the foreum cut it off.

Select
T1.ASSR_FEE,
T1.CAU_DT,
T1.Incident_Date,
T1.Inv_amount,
T1.Rep_Cost,
T2.Co_Name + " " + T2.Surname as DriverName,
T3.Co_Name + " " + T3.Surname as InspectorName
from
cl_details as T1
Left Join tb_pers as T2 on T1.pers_no = T2.ref_id
left join tb_pers as T3 on T1.insp_id = T3.ref_id

[quentin_vigne]

@Anonymous If you replace T1, T2 and T3 by your tablename, does it work ? 

Else, try that : 

Select
T1.ASSR_FEE,
T1.CAU_DT,
T1.Incident_Date,
T1.Inv_amount,
T1.Rep_Cost,
Concat(T2.Co_Name, ' ' ,T2.Surname) as DriverName,
Concat(T3.Co_Name, ' ', T3.Surname) as InspectorName
from
cl_details as T1
Left Join tb_pers as T2 on T1.pers_no = T2.ref_id
left join tb_pers as T3 on T1.insp_id = T3.ref_id 

EDIT : You are doing a left join on the same table twice 

[Anonymous]

That code didnt work.

[Anonymous]

Yes was told the best way was to create another instance of the table to do the different join.

[quentin_vigne]

Try that maybe ? 

Select
cl_details.ASSR_FEE,
cl_details.CAU_DT,
cl_details.Incident_Date,
cl_details.Inv_amount,
cl_details.Rep_Cost,
tb_pers.Co_Name as DriverFirstName,
tb_pers.Surname as DriverSurname
from
cl_details
Left Join tb_pers as T2 on cl_details.pers_no = T2.ref_id
left join tb_pers as T3 on cl_details.insp_id = T3.ref_id 

[Anonymous]

Nope doesnt seem to like the alises, maybe have to do a select statement within the select

[quentin_vigne · Accepted solution]

Well, if it was me here what I would do : 

Create a view for Driver with everything you need

Create a view for Inspector with everything you need

Import your 3 tables in PowerBI and then you can join them and do everything you want, the software is kinda powerful

[Anonymous]

Yeah dont think there is going to be an easy way....

[Anonymous]

Seems to be the syntax - Managed to get it working with.

Select
T1.ASSR_FEE,
T1.CAU_DT,
T1.Incident_Date,
T1.Inv_amount,
T1.Rep_Cost,
T2.CO_Name & ' ' & T2.Surname as DriverName,
T3.CO_Name & ' ' & T3.Surname as InspectorName
FROM
((cl_details T1)
LEFT OUTER JOIN tb_pers T2 on T1.pers_no = T2.ref_id)
LEFT OUTER JOIN tb_pers T3 on T1.insp_id = T3.ref_id

[Anonymous]

It kind of worked 

Select
cl_details.ASSR_FEE,
cl_details.CAU_DT,
cl_details.Incident_Date,
cl_details.Inv_amount,
cl_details.Rep_Cost,
tb_pers.Co_Name as DriverFirstName,
tb_pers.Surname as DriverSurname
from
cl_details
Left Join tb_pers on cl_details.pers_no = tb_pers.ref_id

Though if I am not using aliases how am I going to join the same table twice?

[Anonymous]

Hi QuentiN,

Good thinking will give that a try.

Thanks

[quentin_vigne]

@Anonymous I don't know if you saw the last line on my post because I've edited it 5 seconds before you replied 

You're welcome