Merging all identical tables into one big table

[deepakes2]

Hello - I have a database with bunch of identical tables.

Tables: same table structure auto generated every day.



I want to make all the tables into one table . I want to keep all the records (do not want to take the duplicates out).

Please help me with this . Thanks for looking

[Bulzie]

Probably need more info on your process but if this is just for storage, create a new table (same structure) and use an Append Query to append all the data from each table. Will you continue to create these separate tables each day and need to add that data to the one table or changing your process to write new records to this new combined table?

[deepakes2]

i can define tables
Multiple tables :
Column A
Column B
.
.
Column z

New table:
Column A
Column B
.
.
Column Z

i have to bring all the data that is various small tables into one big table. Please walk me through . I am a beginner. Thanks for your help.

[Bulzie]

1. Copy and Paste one of the tables, select Structure Only and call it tblCombined or something.
2. In a Query window, run this for each table you want to add(change names of tables and fields to reflect your names):

INSERT INTO tblCombined ( ColumnA, ColumnB, ColumnC )
SELECT Table1.ColumnA, Table1.ColumnB, Table1.ColumnC
FROM Table1;

How many tables are you needing to combine?

[deepakes2]

I have various access databases created one every month for about 2 years .
each access file contains identical data auto generated tables end of the day.

My task is to merge all the tables into one table for each month.

january dbo
table 1
table 2
.
.
Table 31

to table : january . I want to keep all the duplicate records.

[Bulzie]

Well once you run that code and verify it works, you should then just have to change the Table1 name to each table you wish to append. You might want to add a field called TableName and add the table you are appending for each record incase you need to know which table the records came from in the future.

INSERT INTO tblCombined ( ColumnA, ColumnB, ColumnC, TableName )
SELECT Table1.ColumnA, Table1.ColumnB, Table1.ColumnC, "Table1"
FROM Table1;

[deepakes2]

Thank you for ur response . Can i just say
INSERT INTO tblCombined ( * )
SELECT *
FROM Table1;

[Bulzie]

Don't know, try it and see.

[deepakes2]

I tried this . did not work. Any other thoughts plz.

Thanks -

[aytee111]

What is the status now? "Did not work" doesn't tell us anything. Post your code and/or your query SQL.

[deepakes2]

I tired the following and did not work.
INSERT INTO tblCombined ( * )
SELECT *
FROM Table1;
I am still looking to get some help with this.

[aytee111]

"Did not work"! Those words should be banned from this forum.

Create the query in query design and get it working. Then copy the SQL from the query into your code.

[Bulzie]

So use the one I posted above, it should work.

So for more clarification, what do you want it all to look like in the end? 1 big database with multiple tables for each month of the 2 years of data? Or you want to keep the data in their own Monthly databases, you just want 1 table for that month? Is this a process that is still active and going to continue(creating a table for each day, etc.) and if so, you should rewrite the process to put the data in the structure you want from the start.

[deepakes2]

I am looking to make a one big table with small table names in one field. plz let me know

[Bulzie]

So here is an idea to manually do this. Others might know of a better way. An automated process could probably be created but would be real involved and not as easy as you have it broken out into multiple databases. So using 2015 as example. In your January 2015 database(that contains 31 tables for each day of that month), create a new table called "tbl2015Jan" that has same fields/structure as your individual tables(add field called ID and make datatype Autonumber and a Text field called TableName). Run this code changing the table name to each days table for January. Also change those column names to whatever they are in your tables.

INSERT INTO tbl2015Jan ( ColumnA, ColumnB, ColumnC, TableName )
SELECT tblJan1.ColumnA, tblJan1.ColumnB, tblJan1.ColumnC, "tblJan1"
FROM tblJan1;

Then change the table name and add the Jan 2 table records to tbl2015Jan table.

INSERT INTO tbl2015Jan ( ColumnA, ColumnB, ColumnC, TableName )
SELECT tblJan2.ColumnA, tblJan2.ColumnB, tblJan2.ColumnC, "tblJan2"
FROM tblJan2;

Do this to all 31 tables. At the end you should have all the records for Jan 2015 in the tbl2015Jan table. Now do this same thing for every month of 2015.

An automated way would be something like:

For i = 1 to 31

Docmd.RunSQL "INSERT INTO tbl2015Jan ( ColumnA, ColumnB, ColumnC, TableName )
SELECT tblJan(i).ColumnA, tblJan(i).ColumnB, tblJan(i).ColumnC, "tblJan(i)"
FROM tblJan(i)"

next i