Join expression not supported

[pritampatil]

Hi All,
I tried to run the attached query in MS-Access but its giving error "Join expression not supported" I am unable to find the cause of it till now, please try to find the same and help me.

access query ----------------------------------------

SELECT
dbo_CAS_CLM_MAST_TB.TRACKING_NO AS [Tracking #],
dbo_CAS_CLM_MAST_TB.ACCOUNT_NO AS [Account #],
dbo_CAS_CLM_MAST_TB.FOLLOW_UP_DT AS [Follow Up Date],
dbo_CLM_COMMENT_TB.COMMENT_TX,
dbo_OPER_PROFILE_TB.FORMAL_NA AS [Formal Name],
dbo_GLOBAL_TYPE_TB.TYPE_DESC_TX AS [File Status],
dbo_CAS_CLM_TPA_TB.LAST_UPDATE_DT AS [Last Updated Date],
dbo_CAS_CLM_MAST_TB.LENDER_ID AS [Lender Id],
dbo_CAS_LENDER_TB.COMPANY_NA AS Lender,
dbo_CAS_CLM_MAST_TB.LOSS_DT AS [Date of Loss],
dbo_CAS_CLM_MAST_TB.DATE_REPORTED_DT AS [Date of Report],
dbo_INV_PARTY_TB.LAST_NA AS [Borrower Last Name],
dbo_INV_PARTY_TB.FIRST_NA AS [Borrower First Name],
dbo_CAS_CLM_MAST_TB.SETTLEMENT_AM AS [Settlement Amount],
dbo_CAS_CLM_MAST_TB.PRODUCER_CD AS Producer,
dbo_CAS_CLM_MAST_TB.REP_COMPLETED_IN,
dbo_CAS_CLM_MAST_TB.DATE_CLOSED_DT,
dbo_OPER_PROFILE_TB.OFFICE_CD AS Office
FROM (((((dbo_CAS_CLM_MAST_TB RIGHT JOIN dbo_CAS_CLM_TPA_TB
ON dbo_CAS_CLM_MAST_TB.TRACKING_NO = dbo_CAS_CLM_TPA_TB.TRACKING_NO)
LEFT JOIN dbo_GLOBAL_TYPE_TB
ON dbo_CAS_CLM_TPA_TB.FILE_STATUS_CD = dbo_GLOBAL_TYPE_TB.TYPE_CD)
LEFT JOIN dbo_CAS_LENDER_TB
ON dbo_CAS_CLM_MAST_TB.LENDER_ID = dbo_CAS_LENDER_TB.LENDER_ID)
LEFT JOIN (dbo_OPER_PROFILE_TB
RIGHT JOIN dbo_OPER_FOLDER_TB ON dbo_OPER_PROFILE_TB.OPER_ID = dbo_OPER_FOLDER_TB.OPER_ID)
ON dbo_CAS_CLM_TPA_TB.TRACKING_NO = dbo_OPER_FOLDER_TB.TRACKING_NO)
LEFT JOIN dbo_INV_PARTY_TB ON dbo_CAS_CLM_MAST_TB.TRACKING_NO = dbo_INV_PARTY_TB.TRACKING_NO)
INNER JOIN dbo_CLM_COMMENT_TB ON dbo_CAS_CLM_MAST_TB.TRACKING_NO = dbo_CLM_COMMENT_TB.TRACKING_NO
WHERE (((dbo_CAS_CLM_MAST_TB.REP_COMPLETED_IN)
Is Null Or (dbo_CAS_CLM_MAST_TB.REP_COMPLETED_IN)<>"Y")
AND ((dbo_CAS_CLM_MAST_TB.DATE_CLOSED_DT)=#1/1/1900# Or
(dbo_CAS_CLM_MAST_TB.DATE_CLOSED_DT)>#1/1/2003#)
AND ((dbo_OPER_PROFILE_TB.DIVISION_CD)="CASU")
AND ((dbo_CAS_CLM_MAST_TB.STATUS_CD)="ACT")
AND ((dbo_GLOBAL_TYPE_TB.GLOBAL_TYPE_CD)="TPS")
AND ((dbo_OPER_PROFILE_TB.APP_CD)="CRV")
AND ((dbo_OPER_FOLDER_TB.ROLE_CD)="CR"
Or (dbo_OPER_FOLDER_TB.ROLE_CD)="CRS")
AND ((dbo_INV_PARTY_TB.INV_PARTY_ROLE_CD)="BRWR"));

[Killer42]

Have you carefully counted your parentheses?

[sherylnance]

Once you put a right join in the mix it starts getting picky. If you change all your lefts to rights then it should be good. just make sure you are getting the data you want. :-)

[MMcCarthy]

You cannot mix up joins like this it doesn't make any sense.

All joins must go in the same direction.

You could try creating a query of right joined tables first.

Then create a query of left joined tables.

You could then try to put them together using a union query.

Select * from query1
Union
Select * from query2;