Union Query? 2

[gregmw]

I create my queries in design mode. I am not flash using sql. How would I go about merging two tables "T1" and "T2" to create a table called "Combined" as set out below.

T1
claim amt1
1 10
2 20
3 30

T2
claim amt2
2 15
5 25
7 35

Combined (what I want)
claim amt2 amt1
2 15 20
5 25
7 35
1 10
3 30

 

[TomKane]

As you say the union query is probably the way to go...


I got this out of the access help:

The following union query renames the Company Name field to "Supplier/Customer Name" in the query output:

SELECT [CompanyName] AS [Supplier/Customer Name], [City]
FROM [Suppliers]

UNION SELECT [CompanyName] AS [Supplier/Customer Name], [City]
FROM [Customers];

Hope it helps....

 

[scriverb]

Create a UNION query in Design mode and paste in the following SQL code:
Select T1.Claim as Claim, Null as Amt2, T1.Amt as Amt1
From T1
Order By T1.Claim
UNION
Select T2.Claim as Claim, T2.Claim as Amt2, Null as Amt1
From T2;

If you don't know how to create a UNION query just get back and I can walk you through it. Bob Scriver

 

[r937]

what you want is called a full outer join

i don't think access supports that syntax, so you can do the same thing with a union of a left and right outer join:

select T1.claim, T2.amt2, T1.amt1
from T1 left join T2
on T1.claim = T2.claim
union
select T2.claim, T2.amt2, T1.amt1
from T1 right join T2
on T1.claim = T2.claim

the above depends on union removing duplicates

you can speed it up by using union all instead, and making the right outer join an exception join, so that rows which match in both tables are returned by only the left join and not both left and right (and therefore require duplcate removal)

select T1.claim, T2.amt2, T1.amt1
from T1 left join T2
on T1.claim = T2.claim
union all
select T2.claim, T2.amt2, T1.amt1
from T1 right join T2
on T1.claim = T2.claim
where T1.claim is null

you cannot run a union into a new table, so you have to split it up into two steps:

select T1.claim, T2.amt2, T1.amt1
into newtable
from T1 left join T2
on T1.claim = T2.claim

insert
into newtable
select T2.claim, T2.amt2, T1.amt1
from T1 right join T2
on T1.claim = T2.claim
where T1.claim is null

good luck

rudy

http://rudy.ca/

 

[scriverb]

Sorry about that. I missed the duplicate records issue. This UNION query will provide the final recordset needed:

SELECT T1.Claim, Null AS Amt2, [T1]![Amt1] AS Books1
FROM T1 LEFT JOIN T2 ON T1.Claim = T2.Claim
WHERE (((T2.Claim) Is Null))
ORDER BY T1.Claim
UNION
SELECT T1.Claim, [T2]![Amt] AS Amt2, [T1]![Amt] AS Amt1
FROM T1 INNER JOIN T2 ON T1.Claim = T2.Claim
UNION
SELECT T2.Claim, [T2]![Amt] AS Amt2, Null AS Amt1
FROM T1 RIGHT JOIN T2 ON T1.Claim = T2.Claim
WHERE (((T1.Claim) Is Null));

Let me know if that works for you.
Bob Scriver

 

[scriverb]

Sorry, typo. My test table had a field called books.

SELECT T1.Claim, Null AS Amt2, [T1]![Amt1] AS Amt1

Let me know how it works with your tables.
Bob Scriver

 

[r937]

bob, yours looks a lot like mine

yours is --

A except B
union
A inner B
union
B except A

mine just combined the inner with the first outer --

A left outer B
union all
B except A

yours should say UNION ALL instead of UNION, because there would not be any duplicates between the results sets of your three subqueries, so avoid the sort

rudy

 

[scriverb]

Great minds run in parallel. Yes, they both will do the job. Bob Scriver

 

[gregmw]

Rudy & Bob thank you both. The two methods do work perfectly although I think Rudy's is slighty faster thanks for your efforts.

 

[scriverb]

r937: Thanks for the tip on the UNION ALL That does make sense. I like you explaination. Bob Scriver