Solved

Combine multiple different Access tables into 1 table

Posted on 2013-06-27
7
1,284 Views
Last Modified: 2013-07-08
I have an Access database with many small tables.  I would like to combine those tables into one large table.  The tables all have different fields.  I don't need to just view the data together with a query...I need to actually collect the data into one table.

Is there a way to combine Access tables into 1 and have it create the fields based on the original tables?  For example, if the source field name doesn't exist in the destination table, it will create it.  If the source field name already exists (there may be duplicates), it just appends to that field in the destination table.

For bonus points, would it be possible to insert a field that contains the name of the original table that the data came from?

See the attached picture for a very simple example.
table-example.jpg
0
Comment
Question by:bruno71
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 6

Accepted Solution

by:
Dulton earned 500 total points
ID: 39282390
I can't say I agree with what you're trying to do, but here's how you could do it.



SELECT
"Table 1" As [OrgSource]
,[first name]
,[last name]
,[number]
,null AS [color]
,null AS [type]
,null AS [size]
INTO [NewTable]
FROM Table1
UNION SELECT
"Table 2"
,null
,null
,null
,[color]
,[type]
,null
FROM Table2
UNION SELECT
"Table 3"
,null
,null
,[number]
,null
,null
,[size]
FROM Table 3;
0
 

Author Comment

by:bruno71
ID: 39282404
I know I could do it manually, but is there a more automated way of doing it?  I suppose I could just create the new table with every field and map the fields one by one, but I was hoping for a quicker, less tedious solution.
0
 
LVL 6

Expert Comment

by:Dulton
ID: 39282416
That code above will query the 3 tables you have and SELECT INTO a new table... all of your data will be in the new table in your requested layout.

If you run that code in a SQL query window, you should end up with your new table just as you want it, fully populated.

I don't know of any wizards or anything to do what you're trying to do, effectively de-normalizing tables...

the code I gave you is as 'automated' of a way as I'm aware of in terms of not only creating the table, but populating it too.
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: 39286188
Not sure I understand what the "resultant" table will represent?
Each record would be "what?"

How are you sure that "Green" should not be the color for the second record in the resultant table?

Can you first explain what the data in each table represents and how they are related?
Also explain what combining all the table will achieve?
0
 

Author Comment

by:bruno71
ID: 39290103
The problem is that the data in all the tables is not related.  Things were originally setup (by someone else) in a bunch of different tables, when really things could have been in the same table.  

Also, field naming is not consistent across the tables.  There may be fields that do have the same name (like 'number' in the example) so I can put that data in the same column.  But then one table might have a field called 'size' and another table has a field called 'dimensions' when really they are the same thing.

I want to combine all the table structures and data into one table so that I can sort through and consolidate it all.  I was just looking for an easy way to combine everything instead of building the master table one field/record at a time.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39292181
Then see  Dulton's first  post
0
 

Author Closing Comment

by:bruno71
ID: 39307049
Well, I guess it was a long shot to find something as easy as I was looking for.  Dulton's solution would work, especially if I needed to do it frequently.  But this was a one-time thing.

I did find that it is possible to move the data, table by table, by doing copy/paste with Excel as an intermediary.  I could select the whole table in Access and copy/paste the data into Excel in text format.  Then I could rearrange the columns in Excel to match the columns in the SQL table.  Then I could copy/paste the data rows from Excel into SQL (SSMS) and it would insert the individual rows as new records.  

Thanks for all the help.
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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 …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
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: …

724 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