COMBINING EXISTING TABLES INTO A SINGLE TABLE

  • Thread starter Thread starter Ranbir Parmar
  • Start date Start date
R

Ranbir Parmar

How can one combine the existing tables automatically into
a single table as opposed to hard-wiring the table names
into a "UNION" SQL Query.
 
How can one combine the existing tables automatically into
a single table as opposed to hard-wiring the table names
into a "UNION" SQL Query.
Click to expand...

One doesn't.

One keeps all like things in one table; and if they are not alike then they
can't be stuck together with a UNION query anyway. There is simply no
excuse for not knowing in advance [1] the names of the tables in a
database. The schema design is the very first thing that gets laid down,
and anything that changes that will spread earthquakes and tsunamis all
round the user interface.

This kind of question is nearly always the result of storing data in table
names. Bad.

Best wishes


Tim F

[1] Oh all right: there are occasional temporary tables that are created
and dropped for special purposes, but they are pretty uncommon and are not
likely to be involved in a query like

For Each tdf in TableDefs
AddToQuery(tdf.Name)
Next tdf

which is what I guess is happening here.
 
not sure what you means. Maybe you can create a Query that does the Union,
and then use that query subsequently/repeately.
 
Thanks a lot for understanding my question correctly and
responding sensible.

I am relatively novice in MSACCESS. However, I have a
situation where I am pooling in the similarlrly structured
tables from 100 (sometimes and may be 110 othertimes)
analysts and need to combine them into a single table for
some data messaging. First thing I like to do is
is "UNIONIZE" them via a single command. The question is
what is the code for that single command. I guess I am
missing something from your sample code below.
-----Original Message-----
How can one combine the existing tables automatically into
a single table as opposed to hard-wiring the table names
into a "UNION" SQL Query.
Click to expand...

One doesn't.

One keeps all like things in one table; and if they are not alike then they
can't be stuck together with a UNION query anyway. There is simply no
excuse for not knowing in advance [1] the names of the tables in a
database. The schema design is the very first thing that gets laid down,
and anything that changes that will spread earthquakes and tsunamis all
round the user interface.

This kind of question is nearly always the result of storing data in table
names. Bad.

Best wishes


Tim F

[1] Oh all right: there are occasional temporary tables that are created
and dropped for special purposes, but they are pretty uncommon and are not
likely to be involved in a query like

For Each tdf in TableDefs
AddToQuery(tdf.Name)
Next tdf

which is what I guess is happening here.
.
Click to expand...
 
Thanks a lot for understanding my question correctly and
responding sensible.

I am relatively novice in MSACCESS. However, I have a
situation where I am pooling in the similarlrly structured
tables from 100 (sometimes and may be 110 othertimes)
analysts and need to combine them into a single table for
some data messaging. First thing I like to do is
is "UNIONIZE" them via a single command. The question is
what is the code for that single command. I guess I am
missing something from your sample code below.
Click to expand...

A MUCH better design (from the outset) would have been to have one
table in the first place, stored in a shared backend database on a
network; each analyst would have a "frontend" database and they could
all enter their own data into this single table, rather than creating
separate tables which must then be laboriously combined.

Given that this might not have been possible in your circumstance, and
in any case that it wasn't done, you'll have to do somewhat of a
chore. I'd suggest creating a generic Append query to append the data
from ANALYST to your master table. I hope and presume that you have
some naming convention so that the 100-110 table names are available
somehow (ideally as a field in some other table). You could then write
a VBA sub which would loop through all the table names, bring up the
Append query, edit its SQL property to change ANALYST to the table
name, and execute the query.

A 110-table UNION query is going to be FAR too large to work in any
case, there's a limit of 64kbytes in a compiled query. AFAIK there's
no simple SQL solution to this dilemma; you'll need to run SQL from
VBA.
 
Back
Top