JOIN expression not supported

[63falcondude]

Can someone help me sort out this SQL in Access? I was getting a Syntax Error before adding the parenthesis to the FROM part of the code and am now getting the error "JOIN expression not supported". I am assuming the error is coming from the FROM part of the code but will include the entire code below.

select 
d3.descrlong as OrderType,
o.no_int as OrderNo, 
op.prodid2 as Model,
op.serialno as Serial,
o.empliddispo,
to_char(o.logtime,'mm/dd/yyyy') as OrderCreationDate, 
to_char(o.closetime,'mm/dd/yyyy') as OrderCloseDate, 
to_char(o.invdat, 'mm/dd/yyyy') as InvoiceDate,
a.no_int as itemno,
a.nam as Name,
ao.price as Price,
ao.LISTPRICE as listprice,
d1.descrlong as Invoicetype,
d2.descrlong as subtype




FROM (((((((ordhead o 
INNER JOIN ordpos op ON o.orders_ik = op.orders_ik)
INNER JOIN orderrole r ON o.orders_ik = r.orders_ik)
LEFT JOIN articleorder ao ON ao.orders_ik = o.orders_ik)
INNER JOIN article a ON ao.article_ik = a.article_ik)
INNER JOIN descriptions d1 ON d1.record_ik = ao.clearingclass)
INNER JOIN descriptions d2 ON d2.record_ik = ao.clearingtyp)
INNER JOIN descriptions d3 ON d3.record_ik = o.typ)




WHERE
o.typ not in (5707)
and o.stat = 5506
and o.closetime between to_date('01-01-2016','mm-dd-yyyy') and to_date('08-31-2016','mm-dd-yyyy')+1
and r.partnerrole = 50301
and ao.typ = 52702
and ao.delflg = 0
and d1.lang = 5102
and d2.lang = 5102
and d3.lang = 5102


;

 

[james_lankford]

don't know if this is your problem, but I know access sometimes trouble with left join and inner joins in the same query

 

[63falcondude]

don't know if this is your problem, but I know access sometimes trouble with left join and inner joins in the same query

Thanks for the response. After some research I have found the same thing. That's probably where my issue is.

 

[Joe4]

You might want to try nesting it in a subquery, and see if that gets around that issue.

 

[63falcondude]

You might want to try nesting it in a subquery, and see if that gets around that issue.

I'll take a look into that, thanks. I am just starting out with Access so everything takes so long to do.

 

[Joe4]

One way you can do it as a beginner is to do "query of queries".
That is, you can have queries based off of other queries.
So, trying doing the query without the LEFT JOIN, they save it. Then use that query you just created in a new query, with your LEFT JOIN. Or vice versa (start of with the LEFT JOIN in the first query, and then create a new query from that query).

 

[63falcondude]

One way you can do it as a beginner is to do "query of queries".
That is, you can have queries based off of other queries.
So, trying doing the query without the LEFT JOIN, they save it. Then use that query you just created in a new query, with your LEFT JOIN. Or vice versa (start of with the LEFT JOIN in the first query, and then create a new query from that query).

This was one of the things that I tried after finding out that the combination of the two JOIN types could be the problem. I got a "Syntax error in JOIN operation" after removing the LEFT JOIN (along with corresponding parenthesis).

 

[Joe4]

You may find you have more luck if you use the Query Builder to build it, and let Access figure out the parentheses instead of trying to write the SQL yourself, especially if you fairly new to it.

 

[63falcondude]

You may find you have more luck if you use the Query Builder to build it, and let Access figure out the parentheses instead of trying to write the SQL yourself, especially if you fairly new to it.

I think that is a good idea. I'll have to try that.

 

[63falcondude]

Figured it out today. I had to delete the spaces in between the code. The working code was

select 
d3.descrlong as OrderType,
o.no_int as OrderNo, 
op.prodid2 as Model,
op.serialno as Serial,
o.empliddispo,
to_char(o.logtime,'mm/dd/yyyy') as OrderCreationDate, 
to_char(o.closetime,'mm/dd/yyyy') as OrderCloseDate, 
to_char(o.invdat, 'mm/dd/yyyy') as InvoiceDate,
a.no_int as itemno,
a.nam as Name,
ao.price as Price,
ao.LISTPRICE as listprice,
d1.descrlong as Invoicetype,
d2.descrlong as subtype
FROM ordhead o 
INNER JOIN ordpos op ON o.orders_ik = op.orders_ik
INNER JOIN orderrole r ON o.orders_ik = r.orders_ik
LEFT JOIN articleorder ao ON ao.orders_ik = o.orders_ik
INNER JOIN article a ON ao.article_ik = a.article_ik
INNER JOIN descriptions d1 ON d1.record_ik = ao.clearingclass
INNER JOIN descriptions d2 ON d2.record_ik = ao.clearingtyp
INNER JOIN descriptions d3 on d3.record_ik = o.typ
WHERE
o.typ not in (5707)
and o.stat = 5506
and o.closetime between to_date('01-01-2016','mm-dd-yyyy') and to_date('08-31-2016','mm-dd-yyyy')+1
and r.partnerrole = 50301
and ao.typ = 52702
and ao.delflg = 0
and d1.lang = 5102
and d2.lang = 5102
and d3.lang = 5102
;