Join Tables with same data

  • Thread starter Thread starter phuser
  • Start date Start date
P

phuser

I have 3 tables from 3 different databases they are exactly the same in
structure. They do however each have their own data, is it possible to join
those 3 tables up? I have given them each their own name for data integrity
but I have to create a duplicate query for each location (tedious) if I am
able to join the tables, I would also have to create an append query to
update weekly. Any help would be greatly appreciated.
 
Maybe an Union query is what you want. It will output all the data as if it
was one table.
NOTE
The union query can not be edited/revised in design view.
Create a select table form one of the tables and then view it in SQL view.
It would look like this --
SELECT Union1.XX, Union1.YY
FROM Union1;

Then edit to add the other tables like --
SELECT Union1.XX, Union1.YY
FROM Union1
UNION SELECT [Union-X].[XX], [Union-X].[ZZ]
FROM [Union-X]
UNION SELECT Union2.XX, Union2.YY
FROM Union2;
 
In addition to what Karl said, since the tables are in different databases,
you'll have to prefix the table name's in each select statement with the
database path. Example being (assuming your three databases are in the
c:\Databases folder under the names of sample1, sample2, sample3:

SELECT field1, field2 from [C:\Databases\sample1.mdb].Table1
UNION
SELECT field1, field2 from [c:\Databasees\sample2.mdb].Table1
UNION
SELECT field1, field2 from [c:\Databasees\sample3.mdb].Table1;
 
Back
Top