SQL/MYSQL: How to display 3 identical tables (from 3 different database files) as if it is a single table

Question

I have an ORACLE table with about a 5 million records, and growing. They are using access to export the data to (From Oracle to Access). They won't change their system, regardless of what I suggest them.

So, in order to be able to work with the data and export it to excel, from access, i had to split the data into 3 database files. In example, let's say I have tables

database1.TBL_A_to_G

database2.TBL_H_to_O

database3.TBL_P_to_Z

Since they come from a single table, they have the same fields. Let's say,

Field1, Field2, Field3...Field30

How can I get a query, in access, where I am getting the data from the 3 tables as if it is one?

I Tried

SELECT Field1, Field2, Field3, Field4, ... Field30 FROM TBL_A_to_G

UNION

SELEC Field1, Field2, Field3, Field4, ... Field30 FROM TBL_H_to_O

-I have to save this query as TBL_A_O and then,

SELECT Field1, Field2, Field3, Field4, ... Field30 FROM TBL_A_O

UNION

SELECT Field1, Field2, Field3, Field4, ... Field30 FROM TBL_P_to_Z

The problem with this approach, is that I can't union the three tables in a single query statement. I get an error message, saying that I can union that many fields. Also, am am still having some performance problems. I need to find a way to do this with performance in mind.

I am open to alternatives as well. The objective is to get the data from those 3 tables (in three different databases) and then export them as queries (With formulas, etc) into excel.

Answer 1

Try using union all instead:

SELECT Field1, Field2, Field3, Field4, ... Field30 FROM TBL_A_to_G
UNION ALL
SELECT Field1, Field2, Field3, Field4, ... Field30 FROM TBL_H_to_O
union all
SELECT Field1, Field2, Field3, Field4, ... Field30 FROM TBL_P_to_Z

With only 30 fields per table, this should be fine for Access.

Answer 2

First you need to know if you want to repeat registers, if so use UNION ALL otherwise use UNION. Problably you have to update the statistics from ACCESS to better performance. About the field problem, aren´t you using the wrong name to acess the data, remember that UNION/UNION ALL uses the first query´s fields names to the result table.