SELECT * FROM
(SELECT A.*
FROM tblExternal1 AS A
IN 'C:\Path\TestExternal1.mdb') AS ExternalA
LEFT OUTER JOIN
(SELECT B.*
FROM tblExternal2 B
IN "C:\Path\TestExternal2.mdb") AS ExternalB
ON ExternalA.External1_ID = ExternalB.External2_ID
docmd.runsql"INSERT INTO tbl_PMS_AllPayors in 'insert mdb pathway' ( Source, State, [Year], ProvID, [MEDICARE #], Payer, Charges, Revenues, [Adjusted RCC], Cost, Profit, market )
SELECT tbl_PMS_AllPayors.Source, tbl_PMS_AllPayors.State, tbl_PMS_AllPayors.Year, tbl_PMS_AllPayors.ProvID, tbl_PMS_AllPayors.[MEDICARE #], tbl_PMS_AllPayors.Payer, tbl_PMS_AllPayors.Charges, tbl_PMS_AllPayors.Revenues, tbl_PMS_AllPayors.[Adjusted RCC], tbl_PMS_AllPayors.Cost, tbl_PMS_AllPayors.Profit, [Market Purchasing Calendar].Market
FROM tbl_PMS_AllPayors in 'insert mdb pathway' LEFT JOIN [Market Purchasing Calendar] in 'insert mdb pathway' ON tbl_PMS_AllPayors.ProvID = [Market Purchasing Calendar].[Provider ID];"
docmd.runsql"INSERT INTO tbl_PMS_AllPayors in 'insert mdb pathway' ( Source, State, [Year], ProvID, [MEDICARE #], Payer, Charges, Revenues, [Adjusted RCC], Cost, Profit, market )
SELECT tbl_PMS_AllPayors.Source, tbl_PMS_AllPayors.State, tbl_PMS_AllPayors.Year, tbl_PMS_AllPayors.ProvID, tbl_PMS_AllPayors.[MEDICARE #], tbl_PMS_AllPayors.Payer, tbl_PMS_AllPayors.Charges, tbl_PMS_AllPayors.Revenues, tbl_PMS_AllPayors.[Adjusted RCC], tbl_PMS_AllPayors.Cost, tbl_PMS_AllPayors.Profit, [Market Purchasing Calendar].Market
FROM tbl_PMS_AllPayors in 'insert mdb pathway' LEFT OUTER JOIN [Market Purchasing Calendar] in 'insert mdb pathway' ON ( tbl_PMS_AllPayors.ProvID = [Market Purchasing Calendar].[Provider ID])"
not sure what exact problem you are trying to solve, please clarify