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
Solved

How do I merge multiple identical tables?

Posted on 2007-11-21
6
2,178 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Single Step Macro 4 41
Display which user(s) is logged in to Access DB over the network 3 34
SetProperty Foreground Colour 5 14
Copying an open file 3 18
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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