?
Solved

How do I merge multiple identical tables?

Posted on 2007-11-21
6
Medium Priority
?
2,185 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 1000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…
Suggested Courses

752 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