outer join with sum

[tk2393]

I need to figure out a query in MS Access to combine two intersecting tables into one, where the intersecting values are summed.

Example:
Table1:
ID value
1 10
2 20
3 30

Table2:
ID value
2 40
3 60
4 80

ResultTable:
ID value
1 10
2 60 ( = 20 + 40)
3 90 ( = 30 + 60)
4 80

[Stewart Ross]

Hi. You're not on the right track if you are looking for a solution using an outer join, as whichever table you take as the 'outer' you will not have a complete set of IDs in the result set.

Instead, consider the use of a Union query to combine the two tables together, then you can sum the value columns in the combined rows from the union query grouped by ID to get the result you need.

I am not posting an SQL solution for this at present, as your question could, it seems to me, relate to a college or university course assignment, and for obvious reasons we cannot provide full-code solutions to such assignment questions. The methodology hints (use a union query feeding into or as part of a group by and sum query) should be enough to get you on the right track with this one for yourself, however.

Welcome to Bytes!

-Stewart

[NeoPa]

Interesting question clearly asked.

Can you let us know if that helped you to the solution?

[tk2393]

Thanks!

In answer to your question, I did post my response, but apparently it either got lost or rejected. I laid out the solution that I came up and asked for a critique. However, I never saw my response posted, and I likewise never received a critique. Do you have any record of my second post?

[NeoPa]

I've looked, and cannot find any reference to another post from you. Sorry.

I would say though, that we do often see comments that look very much as if they should be posts, but sent as Reports. I don't recall any such report personally, but there are so many emails to go through that I don't pay too much attention to them if they're obviously not reportings.

Is it possible you have some record of it?

[tk2393]

Solution

Here's how I ended up doing it. I had to divide it into three different queries: one query for unique values in Table1, one query for unique values in Table2, and one query for the combined values. Then I combined all three with unions.

SELECT * INTO ResultTable
FROM [

select ID, VALUE from Table1 where Table1.ID not in (select ID from Table2)

union

select Table1.ID, Table1.VALUE+Table2.VALUE 
from Table1 inner join Table2 on Table1.ID=Table2.ID

union

select ID, VALUE from Table2 where Table2.ID not in (select ID from Table1)
]

[NeoPa]

Thanks for posting your solution. We always appreciate that here.

What you need here optimally is a FULL OUTER JOIN. This is not supported in Access however.

A cartesian product of the two tables with a WHERE clause selecting only the values that match or include nulls (drop where both values exist and don't match) would be possible, but I suspect would not perform very well.

A solution might be to use two partial OUTER JOINs (a LEFT & a RIGHT, or Table1 LEFT JOIN Table2 as well as Table2 LEFT JOIN Table1) and UNION the two result sets together. I suspect that this may prove more efficient than what you currently have.

NB. Ensure you do not use the ALL predicate of the UNION clause, otherwise you will get repeats of the same results.

[NeoPa]

As an illustration, the following should be worth trying.

Let us know if it works for you :

SELECT *
INTO ResultTable
FROM (SELECT T1.ID,
             T1.Value+T2.Value AS [Value]
      FROM   Table1 AS T1 LEFT JOIN Table2 AS T2
        ON   T1.ID=T2.ID
      UNION
      SELECT T2.ID,
             T2.Value+T1.Value AS [Value]
      FROM   Table2 AS T2 LEFT JOIN Table1 AS T1
        ON   T2.ID=T1.ID)

[FishVal]

Hi, NeoPa.

I'm sure your query will work fine.
Just T2.Value in first subquery and T1.Value in the second should be replaced with zero in a case they are Null.

[NeoPa]

Good spot Fish. That's perfectly true and I overlooked it.

The fixed version is :

SELECT *
INTO ResultTable
FROM (SELECT T1.ID,
             T1.Value+Nz(T2.Value,0) AS [Value]
      FROM   Table1 AS T1 LEFT JOIN Table2 AS T2
        ON   T1.ID=T2.ID
      UNION
      SELECT T2.ID,
             T2.Value+Nz(T1.Value,0) AS [Value]
      FROM   Table2 AS T2 LEFT JOIN Table1 AS T1
        ON   T2.ID=T1.ID)