Search code examples
sqlms-accessms-access-2016

Access: How to sum up fields of multiple tables in a query?

I have a mother table (tblMother) which consists of these fields:

id, text

And I have multiple child tables:

tblChildOne: id, motherID, value

tblChildTwo: id, motherID, value

tblChildThree: id, motherID, value

Looks like that all the child tables have the same data structure. They have not. I just wanna break down the complex tables to the essential point. Consider that all tables have different lengths and that they have many other fields but id, motherID and value in common.

What I am looking for is a query which sums up the value of each child table for a specific motherID. So something like that:

motherID text SumValueChild1 SumValueChild2 SumValueChild3

1 "Test1" 200 300 400

2 "Test2" 150 450 300

3 "Test3" 112 235 472

I am pretty sure that I have to join multiple tables but I just don't get the correct result. Thank's for your help.

Solution

  • You can use correlated subqueries:

    select m.*
       (select sum(child1.value) from child1 where child1.motherID = m.motherID) as sum1,
       (select sum(child2.value) from child2 where child2.motherID = m.motherID) as sum2,
       (select sum(child3.value) from child3 where child3.motherID = m.motherID) as sum4
from mother m;