CODEWITHSUNDEEP
sqldatabasems-access
Leonardo Floriano
Leonardo Floriano

Reputation: 35

SQL Query for get a join between two tables with a multiple condional SUM() in MS Access

It is possible in MS ACCESS 2016 to join a table that is a multi-conditional SUM of another table?

Example:

Table 1 - Columns

ID, NAME, TOTAL

Table 2 - Columns

ID, NAME, IDREF, ROOTID, CUSTO

Data:

Table 1

ID | Name  | Total
---+-------+------
35 |  Test |  "SUM(CUSTO) of ALL ELEMENTS OF TABLE 2 WHERE table2.IDREF = table1.ID **or** table2.ROOTID = table1.ID"

Table 2

ID | Name  | IDREF | CUSTO | ROOTID   |
---+-------+-------+-------+----------+
1  | Test  |  35   |   50  |   0      |
2  | Test  |  35   |   30  |   0      |
3  | ALFA  |  12   |   30  |   0      |
4  | BETA  |  17   |   10  |   35     |

The result should be:

table 1

ID | Name | Total
---+------+------
35 | Test |  90  (50 + 30 from table 2 where idref = 35 and + 10 from table 2 where rootid = 35)

it is very similar as one of my previous question, but i think a multi condional sum is very hard to do in ms-access need some help.

thanks.

Upvotes: 0

Views: 38

Answers (2)

DSR
DSR

Reputation: 722

You can use inner join with multiple ON conditions as below,

SELECT 
    t1.ID,
    t1.Name,
    SUM(t2.CUSTID) AS Total
FROM 
    t2 
    INNER JOIN 
    t1 
ON 
    t2.IDREF = t1.ID
    OR
    t2.ROOTID =t1.ID
GROUP BY
    t1.ID,
    t1.NAME

Output:

ID  Name    Total
35  Test    90

Upvotes: 1

Applecore
Applecore

Reputation: 4099

You can use a couple of sub-queries to get the total for each, and add them together:

SELECT T1.ID, 
NZ((SELECT SUM(T2.Custo) FROM Table2 AS T2 WHERE T1.ID=T2.IDRef),0) +
NZ((SELECT SUM(T2A.Custo) FROM Table2 AS T2A WHERE T1.ID=T2A.RootID) ,0) AS Total
FROM Table1 AS T1;

Regards,

Upvotes: 1

Related Questions