exporting data from multiple table to one

[debanga]

I have five tables with some same fields but with some different primary keys.
now i want to export data from all these tables to another table with some selected fields from each table.

table1 -f1 pk -f2 - f3 -f4
table2-- f1 pk -f2 -g1 -e
table3 -g pk -f1 -h1 -y
table4 -f1 pk -f2 -j1 -y
table5 -f1 pk -f2 -k1 -u

now i want selected data, from all tables in another table, and maintaining the link through f1.. please help me, im trying hard.:banghead:..

 

[cms2012]

Did you try selecting the fields that you wanted in a query and changing the query type to a make table query?

 

[debanga]

please explain little bit.

 

[debanga]

You can use a union query to make one recordset out of several. In a union query all the columns need to be in the same order and of the same data type. So the first selected column is f1 and f1 in all the tables must be the same data type. If you have columns to include that don't exist in all sources, create dummy place holders for them. Column name is not relevant but the names from the first select will be used for the resultset.

Select f1, f2, f8, f99 from tbl1
Union Select f1, f2, null as f8, f99 from tbl2
Union Select f1, null as f2, f3, f4 from tbl3
...[/QUOTE]
how can i insert into a new table. and  in 2nd line what does '...null as f8, f99 from tbl2' means.