CODEWITHSUNDEEP
mysqlms-accessvba
Alex
Alex

Reputation: 1642

Using UNION to add column value with criteria

Basically I would like to add / combine the results from 2 table in a new table as below:

enter image description here

(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

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

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

Related Questions