Reputation: 1642
Basically I would like to add / combine the results from 2 table in a new table as below:
(add up values if colA & colB matches, origin value otherwise)
I tried coming up with below query (in access VBA) but it gives more than I want:
SELECT table1.A, table1.B, table1.C
FROM table1, table2
WHERE table1.A <> table2.A
UNION
SELECT table1.A, table1.B, table1.C+table2.C AS SUM
FROM table1, table2
WHERE table1.A = table2.A AND table1.B = table2.B
UNION
SELECT table2.A, table2.B, table2.C
FROM table1, table2
WHERE table1.A <> table2.A;
though it can be easily done with vlookup in excel; It's not as simple in access. Is using UNION even correct? Also I would like to stick with query, instead of using recordset where the origin data have 10000+ rows
Upvotes: 0
Views: 41
Reputation: 48207
SELECT A, B, SUM(C)
FROM (
SELECT A, B, C
FROM Table1
UNION ALL
SELECT A, B, C
FROM Table2
) T
GROUP BY A, B
ORDER BY A, B
Upvotes: 1