SQL Statement utilizing 5 tables and inner joins (1 Viewer)

[jgier]

Hey Everyone,

I am just stumped with this SQL statement that I wrote :banghead:. I'm getting the basic syntax error on the FROM portion of the SQL statement. Run-time error of 3135, Syntax error in JOIN operation. I thought I had joined everything properly but guess not. What I'm trying to do is take data from 5 different tables, utilize an insert method to another table, and have this all based upon a criteria.

strSQL1 = "INSERT INTO tblCompliancePend_Output (RPT_ID, AGT_TY_IND, ERR_NBR, SHORT_DESC, PLATFORM_CD, BLK_BUS_CD, AOR_SYS_ASSGN_NBR, WA_SYS_ASSGN_NBR, US_POSTL_STATE_CD, GL_CO_CD, GROUP_NBR, " _
& "TRAN_DUE_DT, PR_LN_CD, PR_ID_NBR, YR1_RNWL_CD, TXN_TY_CD, LOB_CD, ERR_DESC, LN_OF_INS_CD1, LN_OF_INS_CD2, LIC_CLASS_CD, LOA_CD, NAIC_ID, ERR_STTS_CD) " _
& "SELECT HUM_TTMDTAC.RPT_ID, HUM_TTMAGT.AGT_TY_IND, HUM_TTMERRM.ERR_NBR, HUM_TTMERRM.SHORT_DESC, HUM_TTMDTAC.PLATFORM_CD, HUM_TTMDTAC.BLK_BUS_CD, HUM_TTMDTAC.AOR_SYS_ASSGN_NBR, " _
& "HUM_TTMDTAC.WA_SYS_ASSGN_NBR, HUM_TTMDTAC.US_POSTL_STATE_CD, HUM_TTMDTAC.GL_CO_CD, HUM_TTMDTAC.GROUP_NBR, HUM_TTMDTAC.TRAN_DUE_DT, HUM_TTMDTAC.PR_LN_CD, HUM_TTMDTAC.PR_ID_NBR, " _
& "HUM_TTMDTAC.YR1_RNWL_CD, HUM_TTMDTAC.TXN_TY_CD, HUM_TTMDTAC.LOB_CD, HUM_TTMDTAC.ERR_DESC, HUM_TTMAGT.LN_OF_INS_CD1, HUM_TTMAGT.LN_OF_INS_CD2, HUM_TTMAGT.LIC_CLASS_CD, HUM_TTMAGT.LOA_CD, " _
& "HUM_TTMAGT.NAIC_ID, HUM_TTMERR.ERR_STTS_CD " _
& "FROM ((((HUM_TTMDTAC INNER JOIN HUM_TTMAAC ON (HUM_TTMDTAC.PLATFORM_CD = HUM_TTMAAC.PLATFORM_CD) AND (HUM_TTMDTAC.BLK_BUS_CD = HUM_TTMAAC.BLK_BUS_CD) AND (HUM_TTMDTAC.GROUP_NBR = HUM_TTMAAC.GROUP_NBR) AND (HUM_TTMDTAC.DETL_TRAN_ID = HUM_TTMAAC.DETL_TRAN_ID)) " _
& "HUM_TTMAAC INNER JOIN HUM_TTMAGT ON (HUM_TTMAAC.COMPL_TRAN_ID = HUM_TTMAGT.COMPL_TRAN_ID)) " _
& "HUM_TTMAGT INNER JOIN HUM_TTMERR ON (HUM_TTMAGT.COMPL_TRAN_ID = HUM_TTMERR_COMPL_TRAN_ID) AND (HUM_TTMAGT.SYS_ASSGN_NBR = HUM_TTMERR.SYS_ASSGN_NBR)) " _
& "HUM_TTMERR INNER JOIN HUM_TTMERRM ON (HUM_TTMERR.ERR_NBR = HUM_TTMERRM.ERR_NBR)) " _
& "WHERE (((HUM_TTMDTAC.RPT_ID) In (9,16,23,28,85,89,90)) AND ((HUM_TTMDTAC.GROUP_NBR)=rs2([GROUP_NBR])) AND ((HUM_TTMERR.ERR_STTS_CD)='02'));"

It's an intense query. Any help would be greatly appreciated.

 

[pbaldy]

Well, this technique should help:

http://www.baldyweb.com/ImmediateWindow.htm

But I'd wonder why you're doing the SQL in code rather than creating a saved query and executing it. It could have criteria. I don't see anything dynamic in there that would justify (IMHO) doing it in code.

 

[jgier]

@pbadly I would have utilized the basic query design but the criteria is based upon a subfrm table. This will allow a user to enter multiple numbers.