Union Query - question

[alee777]

Hi I am hoping someone can assist me. How do i run a sql query within Access to solve the following problem?

Table 1
v1 v2 v3 v4
A B C 5
B C A 10

Table 2
v1 v2 v3 v4
A B C 5
B C A 20
C B A 10

"Union" table 1 and 2 to obtain following results

v1 v2 v3 v4
A B C 10
B C A 30
C B A 10

that's is summing the values based on column "v4"

thanks in advance for your assistance.

 

[shannonp]

Whichever has a complete set of records (either table1 or table2). I will presume table2 has a complete set of records...now, make a standard query with following joins :

table2.v1 <-> table1.v1
table2.v2 <-> table1.v2
table2.v3 <-> table1.v3
* double-click each join and select the option to include all records from table2 and only records from table1 which match...

Now, add table2.v1, table2.v2 and table2.v3 to your query then add a forth Field with the following value:

v4_sum: [table2].[v4] + [table1].[v4]

You may need to slightly vary how you calculate v3 depending on how your tables are formatted.

 

[shannonp]

oops - that last line of text should be v4 not v3...

 

[r937]

what you want is a full outer join, which in access you have to do with a left outer union with an exception right outer --

[tt]select t1.v1, t1.v2, t1.v3
, t1.v4 + iif(isnull(t2.v4),0,t2.v4)
as sumofv4
from t1
left outer
join t2
on t1.v1 = t2.v1
and t1.v2 = t2.v2
and t1.v3 = t2.v3
union all
select t2.v1, t2.v2, t2.v3
, t2.v4
from t2
left outer
join t1
on t2.v1 = t1.v1
and t2.v2 = t1.v2
and t2.v3 = t1.v3
where t1.v1 is null[/tt]

rudy

http://r937.com/