Combine 2 tables and at the same time write it to a third table (1 Viewer)

[Michael.Koppelgaard]

Hi
I want to combine table1 and table2 using union and at the same time write the result to a third table (table3)
I tried this:

SELECT ID INTO TABLE3
FROM Table1;

UNION

SELECT ID INTO TABLE3
FROM Table2;

but I get the error : "an action query cannot be used as a row source"
Is what I'm trying impossible?

 

[Ranman256]

In a SELECT query, take 2 tables,join them,
bring down the fields onto the grid,
then click MAKE table button.
write to the new table.

 

[Michael.Koppelgaard]

In a SELECT query, take 2 tables,join them,
bring down the fields onto the grid,
then click MAKE table button.
write to the new table.

I'm very new in sql. Could pleace write the SQL neaded ?

 

[mike60smart]

Ranman is not talking about SQl. Just create a query and add the 2 tables
Join them and add fields to the grid below the tables.
Then use the Make Table Icon to create the New Table.

 

[Michael.Koppelgaard]

Okay I understand. But when I put in the SQL in the SQL window and click the Make Table button the SQL disappears…
But now in found another solution. I write it all in SQL:


SELECT ID into table3 FROM(
SELECT ID
FROM Table1
UNION
SELECT ID
FROM Table2);

 

[CJ_London]

That code will eliminate any duplicate id’s. If both tables contain an id of 100, it will only appear once in your new table. Is that what you want?

 

[Michael.Koppelgaard]

Yes is that is exactly what I want Otherwise I should use UNION ALL, correct?

 

[Pat Hartman]

Union queries need to be made in SQL view.
Make the union query and save it.
Then make a new query that is a make table query. Select the union query instead of a table as the source.
Save and run.

 

[plog]

I want to combine table1 and table2 using union and at the same time write the result to a third table (table3)

I've got lots of whys:

1. Why do you need to do this? Give me the big picture, where are table1 and table2 coming from? Is this an ongoing process you will run frequently?

2. Why a UNION? Seems like an unneccessary step if the data is going to live in table3. Make an APPEND using Table1 to put data into Table3 and then an APPEND using Table2 to put data into Table3.

3. Why Table3? Why not just put Table1 data into Table2? In the end all your data is in 1 table, so why's it need to be a 3rd table?

4. Again--why do you need to do this? Moving data around is a huge red flag of doing databases wrong. There are some edge cases for it, but this is setting off my senses. What's the big picture?

 

[CJ_London]

Otherwise I should use UNION ALL, correct?

Correct