How do I merge 2 table in 1 and change value from the second table to negative value before merge-CodePudding

Table 1 :

TheName	TheVal1
Blue	2
Red	8
Orange	6
Green	13
Blue	14
Green	25

Table 2 :

TheName	TheVal2
Blue	9
Red	30
Pink	8
Green	10
Blue	3
Green	2

Output excepted :

TheName	TheVal
Blue	4
Red	-22
Pink	-8
Green	26
Orange	6

Calculation details :

Blue = (2 14)-(9 3) = 4
Red = (8)-(30) = -22
Pink = (0)-(8) = -8
Green = (13 25)-(10 2) = 26
Orange = (6)-(0) = 6

What I did try :

So Basicly my query should do the following :

SELECT TheName,SUM(TheVal1) - (SELECT TheName,SUM(TheVal2) FROM Table2 GROUP BY TheName) FROM Table1 GROUP BY TheName

But this throw the following error :

You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT Statement of the subquery to request only one field

I think I have to merge both table in 1 and convert the new values to negatives values so this should give the following table :

TheName	TheVal
Blue	2
Red	8
Orange	6
Green	13
Blue	14
Green	25
Blue	-9
Red	-30
Pink	-8
Green	-10
Blue	-3
Green	-2

And then just execute the following SQL code :

SELECT TheName,SUM(TheVal) FROM NewMergedTable GROUP BY TheName

Can someone help me with this ?

Please note that I am working on ACCESS

CodePudding user response：

You could use a select from suquery with union

select TheName, sum(TheVal) 
from (

    select TheName, sum(TheVal1) TheVal
    from table1 
    group by TheName 
    UNION 
    select TheName, -1*sum(TheVal2)
    from table2 
    group by TheName 

)    t group by TheName