merging all Identical tables (1 Viewer)

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

How many tables and is this a one off or a repetitive task to be performed every day with a new update?

If it's a one off - create your master table then copy and paste. Unless its 100's of tables.

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.

That sounds like a horrible way to store data... you have approximately 24 databases, each one with approx 30 Identical tables .

And you want to now move all the January day table records into a single January table... This goes against all the normal rules of database normalisation.
I think I would create a new master database, with a master data table. Do not call it January. Call it AllMainData

In one of the original databases create a union query something like this (untested) assuming this was the January 2015 Database;

SELECT *, 1 As DayOfMonth, 1 as MonthOfYear , 2015 As DataYear FROM Table1
Union All
SELECT *, 2 As DayOfMonth, 1 as MonthOfYear , 2015 As DataYear FROM Table2
Union All
SELECT * ,3 As DayOfMonth, 1 as MonthOfYear , 2015 As DataYear FROM Table3
etc etc

Run that query - Paste the results into excel.
Add a column Call DataDate and concatenate the added DayOfMonth,Month,Year fields into the DataDate field.

You should now have all the data in one excel sheet, with a record of the date of the table it was in. Either paste or import that into your new main Data table making sure you include your new DataDate field.

Repeat for each database. This will give you a single table with all your data.

You could probably write a clever function / vba sub to do all this for you if you put the DB names into a table, and possibly create the db names automatically if you have them stored centrally, but it will probably be quicker to do it semi-manually as above.

Can you plz simplify the process you just mentioned .

Can you also let me know if there are any other easy ways to do this .

Thanks.

That is basically as simple as it will get, short of you just copying and pasting all the data straight into Excel and manually adding the table and date information, unless your tables already have the date information stored in them. If they have I have no idea why you would store the data into separate tables anyway.

If anyone else has any ideas I'm sure they will post up.

From what I've read, Minty's process is the best one for you. By that I mean, I don't think you have the technical skills required to pull off a more elegant/efficient solution.

I also agree that some sort of function that loops through every database and table in them would be more efficient, however, I don't think you can write that function. Of course, my judgement of your coding ability is based solely on the questions you have asked on this post.

So, my advice is to make a blank database with a table where you want all your data to ultimately end up at. Then take one source database and implement Minty's solution and refine the process until it's as efficient and reproduceable as possible. Then move on to the next database, trying to improve the process as you go. Perhaps you can build a set of queries you can simply copy into each new source database and just execute in order to move the data.

the file is usually too big to copy into excel.

Thank you for the help . I am kind of looking if i can do something like

INSERT INTO mastertable ( * )
SELECT *
FROM Table1;

I dont know if this will work. plz also let me know if there is a code i can copy paste to make this work.
Thanks

You could also use the import function you have in access to import the tables into a temporary holding table.
In that table you can then have the additional columns mentioned above.

Use an update query to inser the information specific to that month.

Use an append query to move the data from the temporary table into your main table.

With a delete query you can empty the temporary table and then start over with the next table.

It will most likely fail horribly if there are relationships between the tables in the imported data but with some practice you will be able to manage that part as well.

Out of interest how many records are you talking about, you may exceed the size of a single Access database?

A modern version of Excel can store over a 1,000,000 rows per workbook...

although we dont know much about your db structure another issue to consider is how to deal with primary/foreign keys between tables if there are any.

Can someone help me write a function to do the above task .

Thanks