Merging Multiple Tables With Same Fields into Single Query

[inouridr]

I have seven data extracts from multiple systems which contain exactly the
same fields and I need to merge via a query into a single dataset. I would
like to avoid the obvious of doing an import/append to create a single table
because the data in the extracts frequently change.

Instead I wanted to create table links to each extract and then create a
query to merge the data. This way the single query dataset would update with
the most recent records, without having to redo the entire import/append
process all over again to update the table.

Is this possible?

Thanks in advance for any assistence!!!

 

[Lord Kelvan]

just use a union query

goto query design and then click view in the menu bar and click sql
view

then type

select * from table1/query1
union all
select * from table2/query2
union all
select * from table3/query3
union all
....etc...

where table/query is the name of the data set

hope this helps

regards
kelvan

 

[Lord Kelvan]

just on a note that will only work if all the tables/queries field
order is the same if not then you need to type select table1/
query1.field1, table1/query1.field2, table1/query1.field3 ... etc ...

 

[inouridr]

Thanks Kelvan!!

This is what I was looking for. I had a little trouble getting the syntax
right, but finally got it.

My miss was that I wasn't supposed to end the query with the "UNION ALL"
clause. It was only suppoed to be entered between each Select statement. I
also was required to bracket my table names.

Below is what I used successfully for those who might like an example in the
future (this example merges three tables):

Select * From [table1 name]
Union All
Select * From [table2 name]
Union All
Select * From [table3 name]


Thanks again!!,

inouridr

 

[Lord Kelvan]

yea well you really shouldnt create tables and fields with spaces it
can create problems and you have to use the [] to attempt to fix it
over all it is better to not put the spaces in to begin with