PLS Help!! Sumif formula in MS Access

[glmleilei]

I've been searching the answer for the whole day, and wont be able to find my answer. Thanks for your help in advance! I'll get straght into it.


My Dataset:
ColunmA ColunmB Value
a b 1
b d 2
a q 3
c a 5
b e 2
d e 10
f q 1
q m 8
. . .
. . .
. . .


In Excel, I would do sumif( ColunmB,a2,Value), which gives me a result:


ColunmA ColunmB Value Result
a b 1 15
b d 2 2


a q 3 15
c a 5 8
b e 2 2
d a 10 2
f b 1 0
q c 8 0
. . . .
. . . .
. . . .


So based on each string in ColunmA, it would search all ColunmB and return an aggregated value.
How can i get the same result by Access/SQL query?


Regards,


Michael

[June7]

This is not simple.

Search forum on keyword SumIf.

Here is one: https://www.accessforums.net/access/...-if-51205.html

Consider:

SELECT Table1.A, Table1.B, Query1.B, Query1.SumOfValue
FROM (SELECT Table1.B, Sum(Table1.Value) AS SumOfValue
FROM Table1
GROUP BY Table1.B) AS Query1 RIGHT JOIN Table1 ON Query1.B = Table1.A;

[glmleilei]

Answer:

SELECT *, (SELECT Sum(Value) FROM myDataset AS T WHERE ColumnB=myDataset.ColumnA) AS Result
FROM myDataset
__________________
CJ_London