1

I have a database that contains 3 tables, let's call them Test1, Test2, Test3

Test1
name |value | value2
A    |0     | 0
B    |1     | 1

Test2
name |value | value2
D    |0     | 0
E    |1     | 1
A    |1     | 1
F    |1     | 1

Test3
name |value | value2
B    |1     | 0
C    |1     | 1
F    |0     | 1

Note that the tables have the same structure and the 'name' cells may repeat.

The result I want is to find unique 'name' cells across all the 3 tables, and get a total sum of corresponding value and value2 from all the 3 tables i.e.

Result
name |value | value2
A    |1     | 1
B    |2     | 1
C    |1     | 1
D    |0     | 0
E    |1     | 1
F    |1     | 2

I can't really figure out a query construction that would do it. I know it would be easier to merge the tables into one but unfortunately I can't do it. Any help/guidelines appreciated.

Des Rock
  • 13
  • 3
  • You can "union" your 3 tables together in a subquery. – Jon Scott Oct 10 '17 at 11:13
  • Possible duplicate of [What is the difference between UNION and UNION ALL?](https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all) – Madhukar Oct 10 '17 at 11:39

1 Answers1

1

Use union all and group by. I think this works in MS Access:

select name, sum(value) as value, sum(value2) as value2
from (select name, value, value2 from table1 union all
      select name, value, value2 from table2 union all
      select name, value, value2 from table3
     ) as t
group by name;

I think some older versions of MS Access did not allow union all in subqueries. In that case, you would need to create a view and use the view for the final query.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Ok, this helped. Had to struggle a bit because apparently MS Access requires listing all columns when grouping so group by name, value, value2; finally allowed to make this query work :) Thanks! – Des Rock Oct 10 '17 at 11:43