Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Error when trying to connect to ODBC to MS Access databse

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.

1 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

 

 

View solution in original post

13 REPLIES 13
quentin_vigne
Solution Sage
Solution Sage

Hi @Anonymous

You forgot the "FROM" 

 

Quentin

Anonymous
Not applicable

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

@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
Not applicable

That code didnt work.

Anonymous
Not applicable

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

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
Not applicable

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

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
Not applicable

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

Anonymous
Not applicable

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
Not applicable

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
Not applicable

Hi QuentiN,

 

Good thinking will give that a try.

 

Thanks

 

 

@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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors