Query on DB2 tables keeps crashing Access 2007

[haydenal]

I've written several successful queries but this one continues to trouble me.

SELECT

[CMSJUVQ].[JVKEY] AS INCIDENT,
[CMSCHRG].[CRCIT#] AS CITATION,
[CMSCOURT].[CCTIME] AS TIME

FROM

((([CMSJUVQ] 

left join [CMSCHRG] on (([CMSJUVQ].[JVKEY] = [CMSCHRG].[CARRKY]) and ([CMSJUVQ].[JVSEQ#] = [CMSCHRG].[CPERS#])) and ([CMSCHRG].[CSOURC] = 4)) 
left join [CMSNAME] on ((([CMSJUVQ].[JVKEY] = [CMSNAME].[NAMKEY]) and ([CMSNAME].[NASRCD] = 4)) and ([CMSNAME].[NAMTYP] = 20)) and ([CMSJUVQ].[JVSEQ#] = [CMSNAME].[NAPSQ#])) 
left join [CMSCOURT] on (([CMSNAME].[NAMKEY] = [CMSCOURT].[CCASE]) and ([CMSNAME].[NASRCD] = [CMSCOURT].[CSRC])) and ([CMSNAME].[NAPSQ#] = [CMSCOURT].[CSEQ1]))

WHERE (([CMSCHRG].[CRCIT#] = '086138137') and ([CMSCHRG].[CSOURC] = 4));

When I run it it tells me "JOIN expression not supported" and highlights

[CMSCHRG].[CSOURC] = 4

on the first join.

Ordinarily I would then enclose

(([CMSJUVQ].[JVKEY] = [CMSCHRG].[CARRKY]) and ([CMSJUVQ].[JVSEQ#] = [CMSCHRG].[CPERS#])) and ([CMSCHRG].[CSOURC] = 4))

in the first join and the problem is taken care of. For whatever reason when I do this, access crashes. Any suggestions as to how I might get this query to run?

 

[jdraw]

I don't understand these parts of the JOIN statement
and ([CMSCHRG].[CSOURC] = 4
and ([CMSNAME].[NASRCD] = 4

These look like parts of a WHERE statement.

This is familiar Left Join syntax: (w3schools)

SQL LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

I would move all of these = some number expressions to be part of the WHERE clause and remove then from the JOIN itself.
I think you have a basic syntax error.

 

[haydenal]

Thank you for the advice. I tried it out as you suggested and it works fine.