djlurch
asked on
How do I merge multiple identical tables?
I have an Access 2003 database with 5 tables. The structure of each table is identical but the content is different. I would like to combine the data into one new table. How do I do this?
I don't think I want to employ a query approach. I literally want to combine them all.
Thoughts?
I don't think I want to employ a query approach. I literally want to combine them all.
Thoughts?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Something like this should work for you.
Public Sub UpdateToConsolidatedTable()
Dim strPath As String, strTableName As String, strDbName As String
Dim fs As Object
Dim strSQL As String
Dim db As Database
Dim tbl As TableDef
Dim strNewTablename As String
Set db = CurrentDb
DoCmd.SetWarnings False
For Each tbl In db.TableDefs
If tbl.Name Like "11" & "*" Then 'pattern match to get correct table
strNewTablename = tbl.Name
CurrentDb.Execute "Insert into tblMyTable select * from " & strNewTablename
DoCmd.DeleteObject acTable, strNewTablename
End If
Next tbl
db.Close
End Sub
djlurch,
< don't think I want to employ a query approach. >
Ummm... can I ask why not?
If you really hate queries, you "could" select all the records from each table and do a "Paste APPPEND" into one table.
The danger here is that, by force of habit, you might click "Paste" not "Paste Append"!
:O
JeffCoachman
< don't think I want to employ a query approach. >
Ummm... can I ask why not?
If you really hate queries, you "could" select all the records from each table and do a "Paste APPPEND" into one table.
The danger here is that, by force of habit, you might click "Paste" not "Paste Append"!
:O
JeffCoachman
jmoss111,
If queries worry djlurch, I don't know if iterating through tabledefs in VBA will fare any better!
:)
Happy Thangsgiving to all!
:)
JeffCoachman
If queries worry djlurch, I don't know if iterating through tabledefs in VBA will fare any better!
:)
Happy Thangsgiving to all!
:)
JeffCoachman
ASKER
This is the solution I employed.
This is really your only option, as queries are the only real tool you have to insert data into another table.
Create a new table with the same design(schema) as your other five, then run this query (air code, so you'll want to rename)
INSERT INTO DestinationTable (Column1, Column2, Column3, ColumnN)
SELECT Column1, Column2, Column3, ColumnN
FROM Table1
INSERT INTO DestinationTable (Column1, Column2, Column3, ColumnN)
SELECT Column1, Column2, Column3, ColumnN
FROM Table2
etc.