By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,813 Members | 2,269 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,813 IT Pros & Developers. It's quick & easy.

Join expression not supported

P: 1
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
Share this Question
Share on Google+
3 Replies


Expert 5K+
P: 8,434
Have you carefully counted your parentheses?
Oct 26 '06 #2

P: 4
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
Expert Mod 10K+
P: 14,534
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.