Solved

How do I merge multiple identical tables?

Posted on 2007-11-21
6
2,177 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:djlurch
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 20329911
>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
 
LVL 26

Accepted Solution

by:
jerryb30 earned 250 total points
ID: 20329925
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
 
LVL 18

Expert Comment

by:jmoss111
ID: 20330007
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20332771
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20332779
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
 
LVL 1

Author Closing Comment

by:djlurch
ID: 31410430
This is the solution I employed.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question