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?
LVL 1
djlurchAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
jerryb30Connect With a Mentor Commented:
select * from table1
union all
select * from table2
union all
select * from table3

etc

and then a maketable query

select * into newTable from UnionQuery
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I don't think I want to employ a query approach.
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.
0
 
jmoss111Commented:
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

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
jmoss111,

If queries worry djlurch, I don't know if iterating through tabledefs in VBA will fare any better!
:)

Happy Thangsgiving to all!
:)

JeffCoachman
0
 
djlurchAuthor Commented:
This is the solution I employed.
0
All Courses

From novice to tech pro — start learning today.