By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,563 Members | 1,066 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,563 IT Pros & Developers. It's quick & easy.

Concatenating many tables

P: 3
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
Oct 24 '08 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,402
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)?
Oct 24 '08 #2

P: 3
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.
Oct 24 '08 #3

NeoPa
Expert Mod 15k+
P: 31,402
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"?
Oct 24 '08 #4

P: 3
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
Oct 24 '08 #5

NeoPa
Expert Mod 15k+
P: 31,402
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.
Oct 25 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.