469,934 Members | 2,648 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

Join expression not supported

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"));
Oct 26 '06 #1
3 7695
Killer42
8,435 Expert 8TB
Have you carefully counted your parentheses?
Oct 26 '06 #2
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. :-)
Oct 26 '06 #3
MMcCarthy
14,534 Expert Mod 8TB
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;
Oct 27 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by steve | last post: by
5 posts views Thread by Eitan M | last post: by
6 posts views Thread by PW | last post: by
3 posts views Thread by janko.klemensek | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.