SQL: Join two SELECTs to calculate difference

[jroof]

I have the following query:
SELECT
CMB_AccountsID, CurrentAmount - Amount AS BalanceAmount
FROM
(SELECT SUM(Fund.Amount) AS Amount
FROM Persons
INNER JOIN Fund
ON Fund.PersonsID= Persons.PersonsID
INNER JOIN Terms
ON Terms.TermsID = Fund.TermsID
INNER JOIN Accounts
ON Accounts.PersonsID = Persons.PersonsID
WHERE Terms.Name = 'Fall 2004'
AND Accounts.AccountsID =
(SELECT DISTINCT AccountsID
FROM Transactions
WHERE Transactions.AccountsID IN (1, 3))
GROUP BY Accounts.AccountsID) A

CROSS JOIN

(SELECT SUM(Transactions.CurrentAmount) AS CurrentAmount,
Account.AccountsID
FROM Transactions
INNER JOIN Terms
ON Transactions.TermsID = Terms.TermsID
WHERE (Terms.Name = 'Fall 2004')
AND Transactions.AccountsID IN (1,3)
GROUP BY Transactions.AccountsID) B



THis query works fine if the AccountID is limited to one row, by nature of the CROSS JOIN. But when I return multiple values in the IN clause, the query will not work. I'm looking to accomplish the logic above but by another method. Any ideas would be greatly appreciated.

[ScottShell]

Its very hard to follow your intent from that code, could you post some table definitions and explain what exactly you want to accomplish?

[mj_walther]

Just by chance will the following query every return more than one row? If so it should be joined by an "in" clause or the query will fail.

AND Accounts.AccountsID =
(SELECT DISTINCT AccountsID
FROM Transactions
WHERE Transactions.AccountsID IN (1, 3))
GROUP BY Accounts.AccountsID) A

it appears that it could return two rows one for AccountsID = 1 and one for AccountsID = 3