Converting T-SQL Left Join Subqueries to MS-Access
===

I need help in converting this T-SQL query to MS ACCESS.  The error that I'm getting is JOIN expression not supported.

T-SQL Query:


    SELECT
        *
    FROM
    (
        SELECT
            [PesoAmount] = CASE WHEN FE.IsoCode IS NULL THEN
                LTRIM(STR(DFCF.CurrencyAmount, 20, 2))
            ELSE
                LTRIM(STR(DFCF.CurrencyAmount * FE.PhpConversionRate, 20, 2))
            END,
            DFCF.TransactionNumber,
            DFCF.AccountNumber,
            DFCF.CountryCd,
            DFCF.TransactionTypeCd,
            DFCF.Time,
            DFCF.Date,
            DFCF.TransactionStatusCd,
            DFCF.TransactionCurrencyCd,
            DFCF.BranchNumber,
            DFCF.RemitterExtPartyCd,
            DFCF.BeneficiaryExtPartyCd,
            DFCF.PostedDate,
            DFCF.AssociateNumber,
            DFCF.ExecutingPartyNumber,
            DFCF.CurrencyAmount,
            DFCF.CurrencyAmountInTxnCcy,
            DFCF.CurrencyAmountInAccountCcy,
            DFCF.SecondaryAccountKey,
            DFCF.RelatedInd,
            DFCF.ThirdPartyInd,
            DFCF.TransactionDescription,
            DFCF.SecurityName,
            DFCF.DealNumber
        FROM
            dbo.DesFactCashFlow DFCF (NOLOCK)                                            LEFT JOIN
            dbo.ForeignExchange   FE (NOLOCK) ON DFCF.TransactionCurrencyCd = FE.IsoCode
    )
                               H                                                                                          LEFT JOIN
        dbo.Ctr                C (NOLOCK) ON H.PesoAmount                           = C.PesoAmountFaceValueSumInsured AND
                                             H.AccountNumber                        = C.AccountNumber                 AND
                                             H.TransactionTypeCd                    = C.TransactionType                   LEFT JOIN
        dbo.TransactionType   TT (NOLOCK) ON H.TransactionTypeCd                    = TT.Code                             LEFT JOIN
    (
        SELECT
            [AccountNumber]               = DDA2.AccountNumber,
            [AccountTypeDesc]             = DDA2.AccountTypeDesc,
            [LineOfBusinessName]          = MAX(DDA2.LineOfBusinessName),
            [AccountCurrencyCode]         = MAX(DDA2.AccountCurrencyCode),
            [AccountCurrencyName]         = MAX(DDA2.AccountCurrencyName),
            [AccountRegistrationTypeDesc] = MAX(DDA2.AccountRegistrationTypeDesc),
            [AccountRegistrationName]     = MAX(DDA2.AccountRegistrationName),
            [AccountName]                 = MAX(DDA2.AccountName),
            [AlternateName]               = MAX(DDA2.AlternateName),
            [AccountOpenDate]             = MAX(DDA2.AccountOpenDate),
            [AccountCloseDate]            = MAX(DDA2.AccountCloseDate),
            [AccountStatusDesc]           = MAX(DDA2.AccountStatusDesc),
            [DormantInd]                  = MAX(DDA2.DormantInd),
            [ProductLineName]             = MAX(DDA2.ProductLineName),
            [ProductCategoryName]         = MAX(DDA2.ProductCategoryName),
            [ProductTypeName]             = MAX(DDA2.ProductTypeName),
            [ProductName]                 = MAX(DDA2.ProductName),
            [ProductNumber]               = MAX(DDA2.ProductNumber),
            [AccountTaxId]                = MAX(DDA2.AccountTaxId),
            [AccountTaxIdTypeCode]        = MAX(DDA2.AccountTaxIdTypeCode),
            [AccountTaxStateCode]         = MAX(DDA2.AccountTaxStateCode),
            [AccountPrimaryBranchName]    = MAX(DDA2.AccountPrimaryBranchName),
            [MailingAddress1]             = MAX(DDA2.MailingAddress1),
            [MailingAddress2]             = MAX(DDA2.MailingAddress2),
            [MailingCityName]             = MAX(DDA2.MailingCityName),
            [MailingStateCode]            = MAX(DDA2.MailingStateCode),
            [MailingStateName]            = MAX(DDA2.MailingStateName),
            [MailingPostalCode]           = MAX(DDA2.MailingPostalCode),
            [MailingCountryCode]          = MAX(DDA2.MailingCountryCode),
            [MailingCountryName]          = MAX(DDA2.MailingCountryName),
            [CurrencyBasedAccountInd]     = MAX(DDA2.CurrencyBasedAccountInd),
            [MaturityDate]                = MAX(DDA2.MaturityDate),
            [OriginalLoanAmount]          = MAX(DDA2.OriginalLoanAmount),
            [CollateralTypeDesc]          = MAX(DDA2.CollateralTypeDesc),
            [CollateralTypeCode]          = MAX(DDA2.CollateralTypeCode),
            [InsuredAmount]               = MAX(DDA2.InsuredAmount),
            [EmployeeInd]                 = MAX(DDA2.EmployeeInd)
        FROM
            dbo.DesDimAccount DDA2 (NOLOCK)
        GROUP BY
            DDA2.AccountNumber,
            DDA2.AccountTypeDesc
    )
                             DDA          ON RTRIM(TT.[Description])                = RTRIM(DDA.AccountTypeDesc)      AND
                                             H.AccountNumber                        = DDA.AccountNumber

MS Access Query with Error:

    SELECT
        *
    FROM
    (((
        SELECT
            IIF(ISNULL(FE.IsoCode), FORMAT(DFCF.CurrencyAmount, "##################.00"), FORMAT(DFCF.CurrencyAmount * FE.PhpConversionRate, "##################.00")) AS [PesoAmount],
            DFCF.TransactionNumber,
            DFCF.AccountNumber,
            DFCF.CountryCd,
            DFCF.TransactionTypeCd,
            DFCF.Time,
            DFCF.Date,
            DFCF.TransactionStatusCd,
            DFCF.TransactionCurrencyCd,
            DFCF.BranchNumber,
            DFCF.RemitterExtPartyCd,
            DFCF.BeneficiaryExtPartyCd,
            DFCF.PostedDate,
            DFCF.AssociateNumber,
            DFCF.ExecutingPartyNumber,
            DFCF.CurrencyAmount,
            DFCF.CurrencyAmountInTxnCcy,
            DFCF.CurrencyAmountInAccountCcy,
            DFCF.SecondaryAccountKey,
            DFCF.RelatedInd,
            DFCF.ThirdPartyInd,
            DFCF.TransactionDescription,
            DFCF.SecurityName,
            DFCF.DealNumber
        FROM
            DesFactCashFlow DFCF                                            LEFT JOIN
            ForeignExchange   FE ON DFCF.TransactionCurrencyCd = FE.IsoCode
    )
                           H                                                                                 LEFT JOIN
        Ctr                C ON H.PesoAmount                           = C.PesoAmountFaceValueSumInsured AND
                                H.AccountNumber                        = C.AccountNumber                 AND
                                H.TransactionTypeCd                    = C.TransactionType)                   LEFT JOIN
        TransactionType   TT ON H.TransactionTypeCd                    = TT.Code)                             LEFT JOIN
    (
        SELECT
            [AccountNumber]               = DDA2.AccountNumber,
            [AccountTypeDesc]             = DDA2.AccountTypeDesc,
            [LineOfBusinessName]          = MAX(DDA2.LineOfBusinessName),
            [AccountCurrencyCode]         = MAX(DDA2.AccountCurrencyCode),
            [AccountCurrencyName]         = MAX(DDA2.AccountCurrencyName),
            [AccountRegistrationTypeDesc] = MAX(DDA2.AccountRegistrationTypeDesc),
            [AccountRegistrationName]     = MAX(DDA2.AccountRegistrationName),
            [AccountName]                 = MAX(DDA2.AccountName),
            [AlternateName]               = MAX(DDA2.AlternateName),
            [AccountOpenDate]             = MAX(DDA2.AccountOpenDate),
            [AccountCloseDate]            = MAX(DDA2.AccountCloseDate),
            [AccountStatusDesc]           = MAX(DDA2.AccountStatusDesc),
            [DormantInd]                  = MAX(DDA2.DormantInd),
            [ProductLineName]             = MAX(DDA2.ProductLineName),
            [ProductCategoryName]         = MAX(DDA2.ProductCategoryName),
            [ProductTypeName]             = MAX(DDA2.ProductTypeName),
            [ProductName]                 = MAX(DDA2.ProductName),
            [ProductNumber]               = MAX(DDA2.ProductNumber),
            [AccountTaxId]                = MAX(DDA2.AccountTaxId),
            [AccountTaxIdTypeCode]        = MAX(DDA2.AccountTaxIdTypeCode),
            [AccountTaxStateCode]         = MAX(DDA2.AccountTaxStateCode),
            [AccountPrimaryBranchName]    = MAX(DDA2.AccountPrimaryBranchName),
            [MailingAddress1]             = MAX(DDA2.MailingAddress1),
            [MailingAddress2]             = MAX(DDA2.MailingAddress2),
            [MailingCityName]             = MAX(DDA2.MailingCityName),
            [MailingStateCode]            = MAX(DDA2.MailingStateCode),
            [MailingStateName]            = MAX(DDA2.MailingStateName),
            [MailingPostalCode]           = MAX(DDA2.MailingPostalCode),
            [MailingCountryCode]          = MAX(DDA2.MailingCountryCode),
            [MailingCountryName]          = MAX(DDA2.MailingCountryName),
            [CurrencyBasedAccountInd]     = MAX(DDA2.CurrencyBasedAccountInd),
            [MaturityDate]                = MAX(DDA2.MaturityDate),
            [OriginalLoanAmount]          = MAX(DDA2.OriginalLoanAmount),
            [CollateralTypeDesc]          = MAX(DDA2.CollateralTypeDesc),
            [CollateralTypeCode]          = MAX(DDA2.CollateralTypeCode),
            [InsuredAmount]               = MAX(DDA2.InsuredAmount),
            [EmployeeInd]                 = MAX(DDA2.EmployeeInd)
        FROM
            DesDimAccount DDA2
        GROUP BY
            DDA2.AccountNumber,
            DDA2.AccountTypeDesc
    )
                             DDA          ON RTRIM(TT.[Description])                = RTRIM(DDA.AccountTypeDesc)      AND
                                             H.AccountNumber                        = DDA.AccountNumber                   