Concatenating many tables

[davec4]

I am a new Access use and need to concatenate (UNION) many tables. I am currently doing it "by hand":

SELECT *
FROM [TestA]


UNION SELECT * FROM [TestB];
UNION SELECT * FROM [TestD];
.....
UNION SELECT * FROM [TestX];

I would like to do this with a loop where I can specify TestA, TestB, .... TestX.
The table names are abitrary, not Table1, Table2....

Any help would be greatly appricated.

Thanks

[NeoPa]

Is this simply to display or do you want to move (APPEND) the data somewhere?

How do YOU determine which tables are to be included (List, String, Table, All tables in database)?

[davec4]

Is this simply to display or do you want to move (APPEND) the data somewhere?

How do YOU determine which tables are to be included (List, String, Table, All tables in database)?

I want to export the table as an Excel spreadsheet. I can generate the list of tables in a driver table.

[NeoPa]

Exporting the results is another question entirely. We can handle that, but not here & now as we are looking at something else.

What is a "driver table"?

[davec4]

Exporting the results is another question entirely. We can handle that, but not here & now as we are looking at something else.

What is a "driver table"?

A table containing the names of the files to be appended.


In a generic sense I want to do the following:

select *
from TableA

do name="TableB","TableD",......"TableX"
union select *
from name;
end;

Hope that makes it simpler.

Thanks

[NeoPa]

Well, that's fairly straightforward then.

The SQL needs to be built up into a string. You need a code-loop that processes through the table (Use recordset processing - Basic DAO recordset loop using two recordsets).

When the loop has terminated whip off the starting UNION ALL (rather than a simple UNION unless you want to lose any potential duplicates) and you should be left with the main part of your SQL string. Tidy it up and you have what you need.