union query many source tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 480 tables that have to be combined into one. Each table has a
different date. I have perfected a Union Query which grabs one table and
puts the name of the table (10/1/04) in the Source field -
SELECT [ID #],[CENSUS],[ADM],[DISCH],"10/1/04" As Source
FROM [10/1/04] UNION ALL

That is all fine. The drag is that I would have to write a very long
statement to have the above statement then go grab data from the table
10/2/04 and name the source field, grab data from the table 10/3/04 and name
the source field, etc.

Can someone take me to the next level and give me a sql statment that would
go to many many linked tables and make them UNION ALL and get their
individual Source names?

Bless your little pea pickin hearts.
 
You will hit some hard limits before you get it all done. This is a real
nightmare. I don't know how you ended up with 480 identical tables or why
anyone would do that.
The proper thing to do would be do combine them all into one table with, if
necessary, a new field to identify the origin of the data. You can do that
with an append query.
But, before you go any further, don't use a name like 10/1/04. The /
character should not be used in a name. It may confuse Access or Windows.
 
Of course it is not good to have that many tables and it is not good to have
them named with "/" in the title. Unfortunately, I do have those tables and
I need to dump them into one.

I can combine them all with a Union Query and I can put anything I want in
the Source field - Bob, Joe, January First Two Thousand.

The trouble I have is that the code I reference above has to be duplicated
480 times and that is cumbersome to say the least.

Is there some code that uses something like a "like" keyword to go and get
multiple tables that are named alike?
--
Thanks


Klatuu said:
You will hit some hard limits before you get it all done. This is a real
nightmare. I don't know how you ended up with 480 identical tables or why
anyone would do that.
The proper thing to do would be do combine them all into one table with, if
necessary, a new field to identify the origin of the data. You can do that
with an append query.
But, before you go any further, don't use a name like 10/1/04. The /
character should not be used in a name. It may confuse Access or Windows.
--
Dave Hargis, Microsoft Access MVP


knowshowrosegrows said:
I have 480 tables that have to be combined into one. Each table has a
different date. I have perfected a Union Query which grabs one table and
puts the name of the table (10/1/04) in the Source field -
SELECT [ID #],[CENSUS],[ADM],[DISCH],"10/1/04" As Source
FROM [10/1/04] UNION ALL

That is all fine. The drag is that I would have to write a very long
statement to have the above statement then go grab data from the table
10/2/04 and name the source field, grab data from the table 10/3/04 and name
the source field, etc.

Can someone take me to the next level and give me a sql statment that would
go to many many linked tables and make them UNION ALL and get their
individual Source names?

Bless your little pea pickin hearts.
Click to expand...
Click to expand...
 
No, not really; however, you could put the code in a loop in VBA. You would
need a way to know what tables need to be included. If there is a scheme to
the naming of the tables, you could the TableDefs collection and look for all
the tables there. Here is an example where we will assume the first 3
characters of the table names are "pdq"

Dim tdf As TableDef
Dim dbf as DAO.Database
Const conSQL As String = "Insert Into....."
Dim strSQL as String

Set dbf = Currentdb
For Each tdf In dbf.TableDefs
If Left(tdf.Name,3) = "pdq" Then
strSQL = Replace(conSQL,"TableNameHere", tdf.Name)
dbf.Execute strSQL, dbFailOnError
End If
Next tdf

Set dbf = Nothing
Set tdf = Nothing

The constant conSQL should be the SQL for the append query. Put the literal
TableNameHere where the source table name will go in the SQL. Now, the
replace function will create a string with the correct table name in it then
execute the query.
--
Dave Hargis, Microsoft Access MVP


knowshowrosegrows said:
Of course it is not good to have that many tables and it is not good to have
them named with "/" in the title. Unfortunately, I do have those tables and
I need to dump them into one.

I can combine them all with a Union Query and I can put anything I want in
the Source field - Bob, Joe, January First Two Thousand.

The trouble I have is that the code I reference above has to be duplicated
480 times and that is cumbersome to say the least.

Is there some code that uses something like a "like" keyword to go and get
multiple tables that are named alike?
--
Thanks


Klatuu said:
You will hit some hard limits before you get it all done. This is a real
nightmare. I don't know how you ended up with 480 identical tables or why
anyone would do that.
The proper thing to do would be do combine them all into one table with, if
necessary, a new field to identify the origin of the data. You can do that
with an append query.
But, before you go any further, don't use a name like 10/1/04. The /
character should not be used in a name. It may confuse Access or Windows.
--
Dave Hargis, Microsoft Access MVP


knowshowrosegrows said:
I have 480 tables that have to be combined into one. Each table has a
different date. I have perfected a Union Query which grabs one table and
puts the name of the table (10/1/04) in the Source field -
SELECT [ID #],[CENSUS],[ADM],[DISCH],"10/1/04" As Source
FROM [10/1/04] UNION ALL

That is all fine. The drag is that I would have to write a very long
statement to have the above statement then go grab data from the table
10/2/04 and name the source field, grab data from the table 10/3/04 and name
the source field, etc.

Can someone take me to the next level and give me a sql statment that would
go to many many linked tables and make them UNION ALL and get their
individual Source names?

Bless your little pea pickin hearts.
Click to expand...
Click to expand...
Click to expand...
 
You are very kind to respond so fast and so thoroughly. Unfortunately, About
75% of the instructions you just gave me are above my skill level. I guess I
need to go ahead and this pretty much manually.

Thanks again.
--
Thanks


Klatuu said:
No, not really; however, you could put the code in a loop in VBA. You would
need a way to know what tables need to be included. If there is a scheme to
the naming of the tables, you could the TableDefs collection and look for all
the tables there. Here is an example where we will assume the first 3
characters of the table names are "pdq"

Dim tdf As TableDef
Dim dbf as DAO.Database
Const conSQL As String = "Insert Into....."
Dim strSQL as String

Set dbf = Currentdb
For Each tdf In dbf.TableDefs
If Left(tdf.Name,3) = "pdq" Then
strSQL = Replace(conSQL,"TableNameHere", tdf.Name)
dbf.Execute strSQL, dbFailOnError
End If
Next tdf

Set dbf = Nothing
Set tdf = Nothing

The constant conSQL should be the SQL for the append query. Put the literal
TableNameHere where the source table name will go in the SQL. Now, the
replace function will create a string with the correct table name in it then
execute the query.
--
Dave Hargis, Microsoft Access MVP


knowshowrosegrows said:
Of course it is not good to have that many tables and it is not good to have
them named with "/" in the title. Unfortunately, I do have those tables and
I need to dump them into one.

I can combine them all with a Union Query and I can put anything I want in
the Source field - Bob, Joe, January First Two Thousand.

The trouble I have is that the code I reference above has to be duplicated
480 times and that is cumbersome to say the least.

Is there some code that uses something like a "like" keyword to go and get
multiple tables that are named alike?
--
Thanks


Klatuu said:
You will hit some hard limits before you get it all done. This is a real
nightmare. I don't know how you ended up with 480 identical tables or why
anyone would do that.
The proper thing to do would be do combine them all into one table with, if
necessary, a new field to identify the origin of the data. You can do that
with an append query.
But, before you go any further, don't use a name like 10/1/04. The /
character should not be used in a name. It may confuse Access or Windows.
--
Dave Hargis, Microsoft Access MVP


:

I have 480 tables that have to be combined into one. Each table has a
different date. I have perfected a Union Query which grabs one table and
puts the name of the table (10/1/04) in the Source field -
SELECT [ID #],[CENSUS],[ADM],[DISCH],"10/1/04" As Source
FROM [10/1/04] UNION ALL

That is all fine. The drag is that I would have to write a very long
statement to have the above statement then go grab data from the table
10/2/04 and name the source field, grab data from the table 10/3/04 and name
the source field, etc.

Can someone take me to the next level and give me a sql statment that would
go to many many linked tables and make them UNION ALL and get their
individual Source names?

Bless your little pea pickin hearts.
Click to expand...
Click to expand...
Click to expand...
 
As one guy with limited knowledge to another ... This may be like
using a butter knife when a screwdiver would be better, but sometimes
one does not have ready access to a screwdriver...

I have no idea if there is a limit to how long an sql code can be, but
if the only problem is manual coding, have you considered building the
code in Excel?

Here's how I test set it up in Excel, by cell:

A1: 10/1/04
B1: =TEXT(A1,"m/d/yy")
C1: =CONCATENATE("SELECT [ID #],[CENSUS],[ADM],[DISCH],""",B1,""" As
Source FROM [",B1,"]")

You end up with this in cell C1:
SELECT [ID #],[CENSUS],[ADM],[DISCH],"10/1/04" As Source FROM
[10/1/04]

Just select cells A1, B1, and C1 and drag the little square at the
lower right corner of cell C1 down to row 480 -- and you'll have all
of your code in column C.

If by chance the dates of your table names are not consecutive, if you
can just get their names into a column of 480 rows, skip column A and
B above, put your list in column B, and Column C will have your code.

I have no idea if the generated code will work, but this can save you
from manual coding

FWIW -- Jesse
 
How many times will you need to do this (i.e., is this a one-time
conversion)?

Setting up a spot of code (as Klatuu is suggesting) would make doing this
repeatable and would take the manual labor out of the picture. TANSTAAFL
(there ain't no such thing as a free lunch) -- it will cost you time and
effort to build and debug the code.

If you are only going to do this one time, and if you experience with code
is limited, then "manual labor" may be your best remaining option... unless
you can persuade someone to do this for you?!

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Use Excel!
Use column A for your dates, and column B for the statement.

in cell A1 put '10/1/04 (as text)
in cell B1 put
="SELECT [ID #],[CENSUS],[ADM],[DISCH],""" & A1 & """ AS Source FROM
[" & A1 & "] UNION ALL "

Enter your dates down the column, and then copy B1 down the rest of
the way.

Paste the final results into a query, and take the final "UNION ALL"
off the last line.

Klatuu's earlier email mentioned hard limits. I don't know how this
method will affect that, but it's worth a try.

Chris M.
 
Back
Top