SQL in Access Query

[richy]

I want to select information from two tables that are identical (one as current information and one has archived information)

Table1
T1Key
T1Name

Table2
T2Key
T2Name

Is there anyway of doing a SQL statement to select all information from both tables? Obviously it's not a time for an inner join as they aren't connected.

[penagate]

select * from table1, table2

[richy]

I've tried that but it doesn't select anything and instead brings back the following format

Table1.T1Key Table1.T1Name, Table2.T2Key, Table2.T2Name in one row

rather than

Table1Item
Table1Item
Table1Item
Table2Item

if that makes any sense.

[DKenny]

You need to do a UNION Query

SELECT T1Key ,T1Name FROM Table1
UNION ALL
SELECT T2Key ,T2Name FROM Table2

[richy]

Thats great. Thanks.

Is there anyway of grouping them together, so if there are two values of for example T1Name being the same then summing up the keys?

I know there is no reason of summing keys, but it keeps things simple using the same column names I've already mentioned.

[DKenny]

Use a SELECT DISTINCT instead of just a SELECT to get unique values.

[si_the_geek]

If I understood correctly, then this should get what you want:

SELECT TName, Sum(TKey)
FROM
  (SELECT T1Key as TKey, T1Name as TName FROM Table1
   UNION ALL
   SELECT T2Key as TKey, T2Name as TName FROM Table2)
GROUP BY TName