Merging two tables

[gregmw]

I am using access 97 and I have two tables T1 & T2 I am joining these using a claim number field. In the join property I can select claims from T1 that match T2 or all claim in T1 plus matched one from T2 or all from T2 and matched from T1. Ideally I want all claims from both tables (but zero duplicates). but I can't work out how to do this unless I use two queries (all T1 and matched from T2 (make a table T3) then another query where I select all from T2 that are not in T1 and append that to T3). If someone has a quicker one step query to merge two tables I would love to hear about it.

Thanks

 

[nealvenables]

Hi,

Does a union query do what you want, e.g.

SELECT DISTINCT Col1, Col2, Col3
FROM T1
UNION
SELECT DISTINCT Col1, Col2, Col3
FROM T2

 

[gregmw]

I create my queries in design mode. I am not flash using sql. How would I go about displaying one record for each claim and then one other field for each table so basically

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

 

[nealvenables]

Hi,

I'm pretty sure that a union is the only viable way of achieving this without getting into a convoluted stack of make-table, update-table, etc. queries.

Step-by-step the stages are:

1. Paste the following SQL statement into Notepad, Word or something similar and replace T1, T2, Amt1 and Amt2 with your actual table and column names.

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

2. In Access create a new query. When it pops up in design mode, put it straight into SQL edit mode by clicking the SQL tab, which is on your Query Design toolbar (normally right under the "File" menu). The query should switch to its SQL view - a white box with "SELECT;" in it.

3. Delete "SELECT;" from the SQL view and paste your updated copy of the SQL statement into the query.

4. Close and save the query.

Good luck.