-
SQL in Access Query
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.
-
Re: SQL in Access Query
select * from table1, table2
-
Re: SQL in Access Query
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.
-
Re: SQL in Access Query
You need to do a UNION Query
Code:
SELECT T1Key ,T1Name FROM Table1
UNION ALL
SELECT T2Key ,T2Name FROM Table2
-
Re: SQL in Access Query
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.
-
Re: SQL in Access Query
Use a SELECT DISTINCT instead of just a SELECT to get unique values.
-
Re: SQL in Access Query
If I understood correctly, then this should get what you want:
Code:
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