I am writing a SQL query with inner join as this
select * from (table1 inner join table2 on table1.city = table2.code)
inner join table3 on table3.col1 = 5 and table3.col2 = 'Hello'
This giving me the error "Join expression not supported".
However, if I change the query like this then there is no error
select * from (table1 inner join table2 on table1.city = table2.code)
inner join table3 on table3.col1 = [SomeColumn] and table3.col2 = [SomeColumn]
Why is Access giving me an error on the first query?
Access does not explicitly support full outer joins, but you can achieve the same effect by using a union query.
Select the control into which you want to enter an expression. If the Property Sheet is not already displayed, press F4 to display it. To manually create an expression, on the Data tab in the property sheet, click the ControlSource property of the text box, and then type = followed by the rest of your expression.
It is something to do with the left join on PNLTERM/TERMS; as it works with a inner and right join but not with a left join. The error is 'Join expression not supported'.
Setting the join properties, also known as join type, will determine what records will be displayed when the query is executed. There are three relationship join types available in relational databases: Inner join. Left outer join.
Very late but I had a similar problem with JOIN expressions on MS Access, like described here Access sometimes needs everything inside the ON part of the query to be inside parenthesis, ie:
SELECT ... JOIN <table> ON (everything here inside the parenthesis) WHERE ...
Why is Access giving me an error on the first query?
Well, like the error message says, that form of a JOIN expression is not supported.
You might want to try the following:
SELECT * FROM table1, table2, table3
WHERE table1.city=table2.code AND table3.col1=5 AND table3.col2='Hello'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With