Combining several tables in MS Access 2007

[masonic35and7]

I work for a school district, and I have just imported 6 Excel
worksheets into Access 2007. Now I have 6 different tables. In each
table all the fields are the same. I need the easiest way to merge
all the tables into one huge table. There are at least 2,500 records
per table if that makes a difference.

I am unfamilar with writing any type of code, so please don't suggest
that unless you can also tell me how to do that.

Thanks

[timmg]

Re: Combining several tables in MS Access 2007

On Mar 26, 10:23 am, masonic35and7 <masonic35a...@ gmail.comwrote:

I work for a school district, and I have just imported 6 Excel
worksheets into Access 2007.  Now I have 6 different tables.  In each
table all the fields are the same.  I need the easiest way to merge
all the tables into one huge table.  There are at least 2,500 records
per table if that makes a difference.

1) Make a new empty table or choose the existing table to be the
central repository of the data.

2) Make a query for all field and records for the first table. Change
the query type to Append and select the destination table as the
target.

3) Execute the query

4) Delete the source table and replace with the next

5) Lather, rinse, repeat until all source tables have been used.

Good luck

Tim Mills-Groninger

[masonic35and7]

Re: Combining several tables in MS Access 2007

On Mar 26, 9:17 am, timmg <tmillsgronin.. .@gmail.comwrot e:

On Mar 26, 10:23 am, masonic35and7 <masonic35a...@ gmail.comwrote:
>

I work for a school district, and I have just imported 6 Excel
worksheets into Access 2007.  Now I have 6 different tables.  In each
table all the fields are the same.  I need the easiest way to merge
all the tables into one huge table.  There are at least 2,500 records
per table if that makes a difference.

>
1) Make a new empty table or choose the existing table to be the
central repository of the data.
>
2) Make a query for all field and records for the first table.  Change
the query type to Append and select the destination table as the
target.
>
3) Execute the query
>
4) Delete the source table and replace with the next
>
5) Lather, rinse, repeat until all source tables have been used.
>
Good luck
>
Tim Mills-Groninger

Tim,
I did as you instructed. I have run into a hurdle. I created a new
empty table, and I selected that as my "Append to" destination. I
click on OK, then I hit run, and it gave me the error message that I
need at least one destination field. What do I do??

[Rich P]

Re: Combining several tables in MS Access 2007

Greetings,

Instead of using queries, lets try a simple code module. First, I
understand you imported 6 excel worksheets. They will be named
something like Sheet1, Sheet2, Sheet3,...

So you have created an empty table that contains the same number of
fields as each of the Sheet tables. If you did not create a table
exactly like the sheet tables -- what you can do is to just copy any one
of the sheet tables and choose only structure but not data. For my
explanation I will call the new table table1.

Now go to the Code Module tab and create a new code module and save it.
It will be saved as module11 I believe, by default. Now copy the
following code from here and paste it into your code module and rename
the sheet tables as necessary.

'--copy this code
Sub AppendData()
DoCmd.RunSql "Insert Into table1 Select * from Sheet1"
DoCmd.RunSql "Insert Into table1 Select * from Sheet2"
DoCmd.RunSql "Insert Into table1 Select * from Sheet3"
DoCmd.RunSql "Insert Into table1 Select * from Sheet4"
DoCmd.RunSql "Insert Into table1 Select * from Sheet5"
DoCmd.RunSql "Insert Into table1 Select * from Sheet6"
End Sub

Now place the mouse cursor anywhere inside

Sub AppendData()

and press the F5 key and all the data from each of the sheet tables will
be appended to table1.

Note: each of the sheet tables must be exactly alike for this to work.
They must all have the exact same number of fields.

Rich

*** Sent via Developersdex http://www.developersdex.com ***