Merging Tables

[GreenWizard]

I'm a novice when it comes to MS Access. Is there a way to merge tables? Example:

Table1

date	color	shape	number	start	end
1/1/2015	red	circle	14	103	135
1/2/2015	blue	triangle	27	131	103
1/3/2015	green	square	10	128	112

<tbody>
</tbody>

Table2

date	color	shape	number	start	end
1/1/2015	orange	rectangle	17	121	146
1/3/2015	black	triangle	11	139	125
1/3/2015	blue	triangle	12	136	149

<tbody>
</tbody>

Table3

date	color	shape	number	start	end
1/2/2015	yellow	circle	27	113	102
1/2/2015	brown	square	18	104	137
1/2/2015	yellow	rectangle	29	107	149

<tbody>
</tbody>

OUTPUT

table1.id	table2.id	table3.id	date	color	shape	number	start	end
1			1/1/2015	red	circle	14	103	135
2			1/2/2015	blue	triangle	27	131	103
3			1/3/2015	green	triangle	10	128	112
	1		1/1/2015	orange	rectangle	17	121	146
	2		1/3/2015	black	triangle	11	139	125
	3		1/3/2015	blue	triangle	12	136	149
		1	1/2/2015	yellow	circle	27	113	102
		2	1/2/2015	brown	square	18	104	137
		3	1/2/2015	yellow	rectangle	29	107	149

<tbody>
</tbody>

MUCH APPRECIATED!!

 

[xenou]

That is not the right structure for a database table.

Instead you want this:


SourceID    Date        Color   Shape       Number Start    End
1           1/1/2015    red     circle      14      103     135
1           1/2/2015    blue    triangle    27      131     103
1           1/3/2015    green   square      10      128     112
2           1/1/2015    orange  rectangle   17      121     146
2           1/3/2015    black   triangle    11      139     125
2           1/3/2015    blue    triangle    12      136     149
3           1/2/2015    yellow  circle      27      113     102
3           1/2/2015    brown   square      18      104     137
3           1/2/2015    yellow  rectangle   29      107     149

 

[GreenWizard]

How can I accomplish this? The following SQL query will merge all the tables data, but I need to know what table the row came from.

For Example,

SELECT * FROM [TABLE1] UNION SELECT * FROM [TABLE2] UNION SELECT * FROM [TABLE3] =

ID# date color shape number start end
1 1/1/2015 red circle 14 103 135
1 1/1/2015 orange rectangle 17 121 146
1 1/2/2015 yellow circle 27 113 102
2 1/2/2015 blue triangle 27 131 103
2 1/2/2015 brown square 18 104 137
3 1/2/2015 yellow rectangle 29 107 149
2 1/3/2015 black triangle 11 139 125
3 1/3/2015 green square 10 128 112
3 1/3/2015 blue triangle 12 136 149


In Row 7 (BOLD), how do I know what table that row's data came from? Is there a way to edit the SQL Query to tag each row with a Table Name?


Thanks again!

 

[Joe4]

Try:

[COLOR=#333333]SELECT *, "Table1" as SourceTable FROM [TABLE1] 
UNION 
SELECT *[/COLOR][COLOR=#333333], "Table2" as [/COLOR][COLOR=#333333]Source[/COLOR][COLOR=#333333]Table[/COLOR][COLOR=#333333] FROM [TABLE2] 
UNION 
SELECT *[/COLOR][COLOR=#333333], "Table3" as [/COLOR][COLOR=#333333]Source[/COLOR][COLOR=#333333]Table[/COLOR][COLOR=#333333] FROM [TABLE3];[/COLOR]

Note if you are doing this from SQL and not Access, change all the double-quotes to single-quotes.

 

[GreenWizard]

Please correct me if I'm wrong, but will this not accomplish my goal?

I want to merge Table1, Table2, and Table3 along with adding an additional column (TABLE_NAME) to specify where the row came from. The UNION ALL will merge everything including all duplicate rows?

SELECT *, 'TABLE1' as TABLE_NAME FROM [TABLE1] UNION ALL SELECT *, 'TABLE2' as TABLE_NAME FROM [TABLE2] UNION ALL SELECT *, 'TABLE3' as TABLE_NAME FROM [TABLE3]

 

[GreenWizard]

Thanks

Try:

[COLOR=#333333]SELECT *, "Table1" as SourceTable FROM [TABLE1] 
UNION 
SELECT *[/COLOR][COLOR=#333333], "Table2" as [/COLOR][COLOR=#333333]Source[/COLOR][COLOR=#333333]Table[/COLOR][COLOR=#333333] FROM [TABLE2] 
UNION 
SELECT *[/COLOR][COLOR=#333333], "Table3" as [/COLOR][COLOR=#333333]Source[/COLOR][COLOR=#333333]Table[/COLOR][COLOR=#333333] FROM [TABLE3];[/COLOR]

Note if you are doing this from SQL and not Access, change all the double-quotes to single-quotes.