Combine many tables in to one large table

[lackeysc]

Thanks for any help on this.

I have about 150 tables with the same datatypes and field names. I
would like to, on a regular basis, create (or append to) a table which
combines them all and adds one extra field which would hold the name
of the source tables.

[Tom van Stiphout]

On Mon, 9 Nov 2009 15:16:07 -0800 (PST), lackeysc <lack...@gmail.com>
wrote:

Not a great design, it appears.
You could try a union query, but I'm not sure if you won't exceed
capacity:
select 'table1", * from table1
union all
select 'table2", * from table2
union all
select 'table3", * from table3
etc.

-Tom.
Microsoft Access MVP

[Salad]

Air code

Create a blank, master table with a tablename field.

Then create an Append query.

It will look something like
Insert Into ....
Select ... From Table

Do a view/sql on it, then copy paste the query to some module in a sub.

Public Sub AppendFiles(strTable As Sting)
strSQL = <sql statement from query paste>
Change the code so that it uses the value of strTable, not the hard
coded value in the Select .... From statement.

Then pass the table name, like
AppendFiles Test
and that will call the sub and append the table Test into the master table.

[lackeysc]

Thanks for your response Salad.

It looks to me like I would have to change the SQL statement for each
table that I am appending to the master. I was hoping to pick up on
the table name automatically since I have so many to append. Is there
a way to do this? In your example I would have to change the name from
"Test" to the actual table name 150 times.

Please let me know if you have a trick to pull out the table names.

Thanks again!

[Salad]

lackeysc wrote:

Here's a quick sub to display all table names (linked or not)
Public Sub T()
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
If Left(tdf.Name, 4) <> "MSYS" Then
'excludes system file names.
'Lists table name and the connect string
'there will be a connect string if linked
Debug.Print tdf.Name & " Connect String: " & tdf.Connect
End If
Next
MsgBox "Done. All tables listed in immediate window."
End Sub

If you have programming prowess to create a query and copy paste it to a
variable. Ex:
Dim strSQL As String
Dim intFor as Integer
strSQL = <pasted SQL statement from View/SQL in query window>
Docmd.SetWarnings False 'don't view warning messages
Docmd.RunSQL strSQL
Docmd.SetWarnings True
and the table name was Table1 you could use the Replace() function to
update the name. Ex:
X = "Jon"
? replace(x,"n","e")
Joe
by modifying T, maybe making it a function.

Now how you can differentiate the table names to append from those that
are to not be included is beyond me. I suppose you have some sort of
naming scheme to differentiate standard names from the others.

[lackeysc]

That is exactly what I was looking for. This will save me lots of
time. Thanks so much!!

[Salad]

lackeysc wrote:

:). That's what this group is for.