Solved

How do I merge multiple identical tables?

Posted on 2007-11-21
6
2,183 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 66

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
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.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

696 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