I have 4 Tables in Access - can I combine them?

[officespace]

Every quarter, I import the quarterly sales file (in Excel format) into Access. Each quarterly file is HUGE - about 800,000 to 900,000 rows and around 20 columns. Now, I have all 4 quarters imported into Access.

Each file takes around 2.5 hours to import into Access - so instead of taking 10 hours to import all the files over again - I was wondering if there is a way to combine all 4 quarters into ONE table.



Any help would greatly be appreciated. Thanks!!

[RickM]

You can create a new blank table and Append each table into the single new table.

[officespace]

so basically, i have to load all the files over again, correct?

i was hopping i could just merge all the tables into one since they are already loaded into Access. when you say APPEND, i would have to RE-LOAD the files - is that right??

thanks!

[RickM]

No, you do not have to relaod them. You can copy one of the table as structure only. The ncreate an append query to write the dats from the existing quarterly table to the new Year table. Do you know how to create an append query?

[officespace]

no, i dont think so. can you please help??????

they only thing i really know how to do is import files to Access and then export smaller compact files to Excel.

[RickM]

Ok. first find one of your quarterly tables and right click on it and select copy. Then richt click again and select paste. Select Structure and data and type in the name of your new table (ie: YearEnd2008). You should now have a table identical to the quarterly table.
Next you need to create an append query using the table (let's says Q2) select append to year table and all the fields. If you run this query it will write all the records from the Q2 table to your newly created table.
Do this same thing for the rest of the tables.
Let me know when you get stuck.

[Ahmed Elalfy]

Dear,
I think union query can solve this problem.
If you have 4 tables in access and you need to show all of them in one table you can do the following:
create new query
press VIEW and select SQL VIEW
write select * from table1 union select * from table2 union select * from table3 union select * from table4;

Please try and feedback

Ahmed