First, no square brackets allowed in a subquery in a FROM clause.

Second the JOIN should be between the two subqueries and the ON clause
after the two subqueries.

SELECT po.merchant_order_id AS order_id
, po.source_currency_code AS curr
, po.pymt AS po_pymt
, pt.pymt AS pt_pymt
FROM

(SELECT MERCHANT_ORDER_ID
, SOURCE_CURRENCY_CODE,
Sum(ORDER_TOTAL+COUNTRY_TAX_TOTAL) AS PYMT
FROM tblRC_PAYMENT_ORDERS
GROUP BY MERCHANT_ORDER_ID, SOURCE_CURRENCY_CODE)  AS po

RIGHT JOIN

 (SELECT ORDER_ID, TRANSACTION_CURRENCY, Sum(PAYMENT) AS PYMT
FROM tblRCN_PAYMENT_TRANSACTION GROUP BY ORDER_ID
, TRANSACTION_CURRENCY)  AS pt

ON po.MERCHANT_ORDER_ID = pt.ORDER_ID;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County