How to sum a column where another column is equal to other table

I have 2 tables, Assets and Main. I want to create a query that will total the transactions in Main, grouped by each account in Assets. But there’s a catch: sometimes the amt needs to be summed as a positive and sometimes as a negative.

In Assets, I have the columns Account and Descript. Account holds “1001”, and others, as text; Descript is just text.

Account    Descript
--------------------------
1001       Cash
1101       Receivable

In Main, I have Amt, Ac1, and Ac2.

• Amt holds amounts that we need to sum
• Ac1 and Ac2 hold account numbers from Assets as text

In Main, when an account is marked in Ac1, the transaction is a positive for that account. When an account is marked in Ac2, the transaction amount is a negative for that account.

Say, for one record, in Main:

1. You have -1000.00 in “Amt”, you have “1001” in “Ac1”, and “1101” in “Ac2”.
2. You have 2000.00 in “Amt”, you have “1001” in “Ac1”, and “1101” in “Ac2”.

Data:

Amt    Ac1    Ac2
-------------------
-1000  1001   1101
2000   1001   1101

so then the expected result needs to be:

Account    Descrip    TtlAmt
-------------------------------
1001       Cash        1000.00
1101       Receivable -1000.00

I have some code but I’m not sure if it’s helpful.

SELECT 
    Asset.Account, Asset.Descrip AS Expr1, 
    SUM(Main.Amt) AS SumOfAMT, SUM(Main.Amt) AS Expr2
FROM 
    Asset 
LEFT JOIN 
    Main ON (Asset.ACCOUNT = Main.AC2) OR (Asset.ACCOUNT = Main.AC1)
GROUP BY 
    Asset.Account, Asset.Descrip;

Just to be super clear, I also have tables called “Liability”, “Expense”, etc. But I felt that we can focus on just the one query here, as the rest should fall into place with some guidance.

I know this has nothing to do with problem at hand, but in Excel I use the following formula to accomplish this.

SUM(SUMIF([sum range], [criteria range], [criteria]), SUMIF([sum range], [criteria range], [criteria])*-1)

I thought it may be helpful to explain my end goal.

Answer

Assuming there is a unique identifier field in Main.

Consider:

Query1

SELECT ID, "Ac1" AS Src, Ac1 AS Act, Amt FROM Main
UNION SELECT ID, "Ac2", Ac2, Amt*-1 FROM Main;

Query2

SELECT Query1.Act, Assets.Descrip, Sum(Query1.Amt) AS SumOfAmt
FROM Assets INNER JOIN Query1 ON Assets.Account = Query1.Act
GROUP BY Query1.Act, Assets.Descrip;

All in one

SELECT Query1.Act, Assets.Descrip, Sum(Query1.Amt) AS SumOfAmt
FROM Assets INNER JOIN
(SELECT Ac1 AS Act, Amt FROM Main
UNION SELECT Ac2, Amt*-1 FROM Main) AS Query1 
ON Assets.Account = Query1.Act
GROUP BY Query1.Act, Assets.Descrip;