Union Query

[guptaa13]

I have 5 tables in access 2010, 4 of them have data in them and I need to populate the 5th table with all data from the 4 tables. I know you have to create a Union query, but i dont know the sql statement

Table 1 - data


table 2 - data
table 3 - data
table 4 - data
table 5 - combine data from table 1, table 2, table 3, table

Any help would be very helpful

[ssanfu]

Googled "access union query"
Lots of returns. Here are two:
http://office.microsoft.com/en-us/ac...010206109.aspx
http://www.techonthenet.com/sql/union.php

I do wonder if all of the tables have the same structure? Why copy data from 4 tables into a 5th?
Kinda defeats the purpose of having a relational database...

[June7]

As Steve asks, are the tables same structure? Is this a one-time event to get proper data structure? Will probably need another field in table 5 for a category value.

Run 1 INSERT using UNION as source or run 4 INSERT actions.

[guptaa13]

All the fields are same in the table, but they are different sources, so I want to have one master table.

All companies is the 5th table, and it has all the same fields as the other 4 tables

I was trying the following statement and it is giving an error

insert into [all_companies] SELECT 01_2013_CSMMI203.*
FROM 01_2013_CSMMI203 UNION
select 02_2013_CSMME107-002.*
from 02_2013_CSMME107-002;

[June7]

Where did you build that sql? In query design view or in VBA?

If you use the * wildcard, all fields must be in the same order in the table structure, otherwise enumerate the fields in each SELECT line. The first SELECT line sets the field names.

Do you need a category value to be able to sort/filter the records?

Build the UNION query object and save it. Then build an INSERT query object that uses the UNION as source. For some reason, forum is not letting me type UNION in the example query.

SELECT *, 1 AS Category FROM 01_2013_CSMMI203
______ SELECT *, 2 FROM 02_2013_CSMME107-002
______ SELECT *, 3 FROM ...
______ SELECT *, 4 FROM ...;