Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to combine multiple tables in Access 2010 into single table

Posted on 2014-09-03
7
Medium Priority
?
12,516 Views
Last Modified: 2014-09-12
Hello,

I have 7 tables of information, all tables have the same fields.  I would like to combine the tables into 1 master table and then remove the previous ones.  My intent to do this is so I then have just one table to work with in trying to isolate and remove duplication.  Any and all assistance would be greatly appreciated.
0
Comment
Question by:FreddyBass
[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
7 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40301936
INSERT INTO maintablename SELECT * FROM firstoldtable
INSERT INTO maintablename SELECT * FROM secondoldtable
INSERT INTO maintablename SELECT * FROM nextoldtable

etc...
0
 

Author Comment

by:FreddyBass
ID: 40301982
Hi Neilsr,

Thank you for your input, where to put what you have written?

INSERT INTO maintablename SELECT * FROM firstoldtable
INSERT INTO maintablename SELECT * FROM secondoldtable
INSERT INTO maintablename SELECT * FROM nextoldtable
0
 
LVL 37

Accepted Solution

by:
Neil Russell earned 1500 total points
ID: 40301990
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 31

Expert Comment

by:hnasr
ID: 40302004
Try:

SELECT * INTO mainTable
FROM (SELECT * FROM tbl1 UNION  SELECT * FROM tbl2 ...  UNION SELECT * FROM tbl7)

... to be replaced by UNION for all other remaining tables
0
 

Author Comment

by:FreddyBass
ID: 40302013
Hello Neilsr,

I will have a look at the following link and let you know if that provides the solution I am looking for.

Thank you

Fred
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40303643
It might be better to do this operation in VBA code.  If each table has an ID field as the key field, you won't be able to append to that field.  Also, you may need to check for duplicates before adding a record.  In some cases it might be more appropriate to update an existing record rather than adding a new one.  here is some typical code for an "append or update" choice:
      strSearch = "[Code] = " & lngCode
      Debug.Print "Search string: " & strSearch
      rstTarget.FindFirst strSearch
      
      If rstTarget.NoMatch = True Then
         'Add new record
         rstTarget.AddNew
         rstTarget![Code] = lngCode
         rstTarget![TechName] = strTech
         rstTarget![WorkDate] = dteWork
         rstTarget![StartTime1] = rstSource![StartTime]
         rstTarget![EndTime1] = rstSource![EndTime]
         curRate = CCur(Nz(rstSource![Rate]))
         rstTarget![Rate] = curRate
         rstTarget.Update
      Else
         'Update existing record
         rstTarget.Edit
         rstTarget![StartTime2] = rstSource![StartTime]
         rstTarget![EndTime2] = rstSource![EndTime]
         rstTarget.Update
      End If

Open in new window

0
 

Author Closing Comment

by:FreddyBass
ID: 40319945
Thank you for providing me the link, I was able to find more information there for what I am trying to do
0

Featured Post

Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

715 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