How do you combine several tables into one table in Access?

G

Guest

I am trying to combine nearly 100 tables ( all of the same structure) into
one. I have read up on the Union query and am concerned that if I convert it
into a select query, I will loose the SQL statements. I'm am new to Access
and just need this one question answered to make things work.

Thanks,
 
G

Guest

Hi Gwendolyn,

100 tables.....ugggghhh! I do not envy your task.
Create a new select query, which is based on your union query. Make sure
that it is returning the correct records. The first time around convert the
select query into a Make-Table query. This is an option on the query menu in
query design view.

To be honest, I'm not sure how many select queries that you can string
together with the UNION keyword, but it is likely not 100. You might want to
break the task up into more manageable chunks by creating union queries that
combine the data from 5~10 tables at a time.

After you run the make-table query successfully, create another union query
to collect the data from another 5~10 tables. Create another select query
that is based upon this union query. This time, however, convert the select
query into an append query. Append the current recordset into the table that
you created when you ran the make-table query the first time.

Good Luck!
Tom
______________________________________

:

I am trying to combine nearly 100 tables ( all of the same structure) into
one. I have read up on the Union query and am concerned that if I convert it
into a select query, I will loose the SQL statements. I'm am new to Access
and just need this one question answered to make things work.

Thanks,
 
G

Guest

Thank you Tom! I am going to make a copy of the database and play with it
until I get it right. I'm so happy that I stumbled upon this discussion
group.

Thank you again!

Gwendolyn
 
G

Guest

They are someone similar but not exactly. I could change the names of the
tables to a format similar to the one in your example. Will this help?
 
G

Guest

Hi Gwendolyn,

I'm glad I could help. Post back if you have any problems.

If my answer has helped you, please sign back in to Microsoft's Online
Community and answer yes to the question that reads "Did this post answer the
question?"


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
______________________________________

:

Thank you Tom! I am going to make a copy of the database and play with it
until I get it right. I'm so happy that I stumbled upon this discussion
group.

Thank you again!

Gwendolyn
______________________________________

:

Hi Gwendolyn,

100 tables.....ugggghhh! I do not envy your task.
Create a new select query, which is based on your union query. Make sure
that it is returning the correct records. The first time around convert the
select query into a Make-Table query. This is an option on the query menu in
query design view.

To be honest, I'm not sure how many select queries that you can string
together with the UNION keyword, but it is likely not 100. You might want to
break the task up into more manageable chunks by creating union queries that
combine the data from 5~10 tables at a time.

After you run the make-table query successfully, create another union query
to collect the data from another 5~10 tables. Create another select query
that is based upon this union query. This time, however, convert the select
query into an append query. Append the current recordset into the table that
you created when you ran the make-table query the first time.

Good Luck!
Tom
______________________________________

:

I am trying to combine nearly 100 tables ( all of the same structure) into
one. I have read up on the Union query and am concerned that if I convert it
into a select query, I will loose the SQL statements. I'm am new to Access
and just need this one question answered to make things work.

Thanks,
 
L

LGC

Well, rather than manually appending one hundred tables, it could all be
done with a little code if there is a way to determine which tables you are
appending:


Public Function AppendTables()

Dim i As Integer

For i = 1 To 4
DoCmd.RunSQL ("INSERT INTO TableNew ( Field1, Field2, Field3 )" _
& " SELECT Field1, Field2, Field3 FROM Table" & Trim(CStr(i)) &
";")
Next

End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top