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:

You have -1000.00 in “Amt”, you have “1001” in “Ac1”, and “1101” in “Ac2”.
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;

Advertisement

Answer