Thank you for looking at this post.
I am building a database in which I need to pull data from an Oracle server. I have found, I need to change my SQL language to pull data from the Oracle server, which isn't too challenging for me, if I pull it wide open.
Where I am struggling is trying to join 3 tables together. I do not have a strong understanding of SQL, so hence the struggle.
Below is the code from Access and then below that is my attempt to pass it to Oracle.
SQL before trying to convert to Oracle SQL
SELECT AFTERTREATMENT_MAIN_CEM_BUILD.JOB_NO, AFTERTREATMENT_MAIN_CEM_BUILD.BUILD_NO, AFTERTREATMENT_MAIN_CEM_BUILD.PRODUCT_ID, AFTERTREATMENT_MAIN_CEM_BUILD.SERIAL_NO, AFTERTREATMENT_MAIN_CEM_BUILD.ROUTE_TO, AFTERTREATMENT_MAIN_CEM_BOM.PART_NO, AFTERTREATMENT_MAIN_CEM_BOM.REV, AFTERTREATMENT_MAIN_CEM_BOM.QUANTITY, AFTERTREATMENT_MAIN_CEM_BOM.TOTAL_QUANTITY, AFTERTREATMENT_MAIN_CEM_BOM.DESCRIPTION, AFTERTREATMENT_MAIN_CEM_BOM.BOM_LEVEL, AFTERTREATMENT_MAIN_CEM_BOM.SEQ, AFTERTREATMENT_MAIN_CEM_BOM.TRACER, AFTERTREATMENT_MAIN_CEM_BOM.CHILD_TRACER, AFTERTREATMENT_MAIN_CEM_BOM.IN_OPER_BOM, AFTERTREATMENT_MAIN_CEM_BOM.MAKE_PART, AFTERTREATMENT_MAIN_CEM_BOM.SHEET_STEEL, AFTERTREATMENT_MAIN_CEM_BOM.SEP_WORK_ORDER, AFTERTREATMENT_MAIN_CEM_BOM.PARENT_SEP_WORK_ORDER, AFTERTREATMENT_MAIN_CEM_BOM.KIT, AFTERTREATMENT_MAIN_CEM_BOM.DELIVER_TO, AFTERTREATMENT_MAIN_CEM_BOM.SERIAL_NO, AFTERTREATMENT_MAIN_CEM_BOM.CHILD_SHEET_STEEL, AFTERTREATMENT_MAIN_CEM_BOM.UNIT_OF_MEASURE, AFTERTREATMENT_MAIN_CEM_BOM.PART_TYPE
FROM (AFTERTREATMENT_MAIN_CEM_BUILD INNER JOIN JobBuildNum_Conversion_Gray ON (AFTERTREATMENT_MAIN_CEM_BUILD.BUILD_NO = JobBuildNum_Conversion_Gray.BN_Txt) AND (AFTERTREATMENT_MAIN_CEM_BUILD.JOB_NO = JobBuildNum_Conversion_Gray.JN_Txt)) INNER JOIN AFTERTREATMENT_MAIN_CEM_BOM ON (JobBuildNum_Conversion_Gray.BN_No = AFTERTREATMENT_MAIN_CEM_BOM.BUILD_NO) AND (JobBuildNum_Conversion_Gray.JN_No = AFTERTREATMENT_MAIN_CEM_BOM.JOB_NO)
WHERE (((AFTERTREATMENT_MAIN_CEM_BUILD.SERIAL_NO) Like "*" & [Forms]![Test_EnterPart]![Serial] & "*") AND ((AFTERTREATMENT_MAIN_CEM_BUILD.ROUTE_TO) Not Like "Complete"));
Attempt to Passthrough to Oracle
SELECT JOB_NO, BUILD_NO, PRODUCT_ID, SERIAL_NO, ROUTE_TO, PART_NO, REV, QUANTITY, TOTAL_QUANTITY, DESCRIPTION, BOM_LEVEL, SEQ, TRACER, CHILD_TRACER, IN_OPER_BOM, MAKE_PART, SHEET_STEEL, SEP_WORK_ORDER, PARENT_SEP_WORK_ORDER, KIT, DELIVER_TO, SERIAL_NO, CHILD_SHEET_STEEL, UNIT_OF_MEASURE, PART_TYPE
FROM (AFTERTREATMENT_MAIN_CEM_BUILD INNER JOIN JobBuildNum_Conversion_Gray ON (BUILD_NO = JobBuildNum_Conversion_Gray.BN_Txt) AND (JOB_NO = JobBuildNum_Conversion_Gray.JN_Txt)) INNER JOIN AFTERTREATMENT_MAIN_CEM_BOM ON (JobBuildNum_Conversion_Gray.BN_No = BUILD_NO) AND (JobBuildNum_Conversion_Gray.JN_No = JOB_NO)
WHERE (((AFTERTREATMENT_MAIN_CEM_BUILD.SERIAL_NO) Like "*" & [Forms]![Test_EnterPart]![Serial] & "*") AND ((AFTERTREATMENT_MAIN_CEM_BUILD.ROUTE_TO) Not Like "Complete"));