Combining multiple tables into one

[Haas C]

Hi all,

I am a newbie who goes through tasks in apainfully slow and in an
inefficient way in Access. I've been experimenting here and there and
have figured out a few things, but for the most part, I still need
help. Here's a quick question that can help me get rolling with a new
project. I have multiple tables, all have the same field names and
formats...what is the quickest way to merge them all into one large
table? I've been doing an Append Query one by one, but that takes a
while, given that i have 45 tables. The table I want all the other
tables "merged" or appended to is tblAllStocks. Is there a faster way
to do this instead of Appening one table at a time?

Thank you for your help in advance,
-Haas

[Tom van Stiphout]

On Mon, 20 Oct 2008 06:33:59 -0700 (PDT), Haas C <ha*****@yahoo.com>
wrote:

There is no faster way, unless you are fluent with VBA, but then you
probably would not have asked the question.
If you have to do this repeatedly, you can create 45 queries, and run
them in sequence from a single macro.

-Tom.
Microsoft Access MVP

>Hi all,

I am a newbie who goes through tasks in apainfully slow and in an
inefficient way in Access. I've been experimenting here and there and
have figured out a few things, but for the most part, I still need
help. Here's a quick question that can help me get rolling with a new
project. I have multiple tables, all have the same field names and
formats...what is the quickest way to merge them all into one large
table? I've been doing an Append Query one by one, but that takes a
while, given that i have 45 tables. The table I want all the other
tables "merged" or appended to is tblAllStocks. Is there a faster way
to do this instead of Appening one table at a time?

Thank you for your help in advance,
-Haas

[Keith Wilby]

"Haas C" <ha*****@yahoo.comwrote in message
news:05**********************************@n1g2000p rb.googlegroups.com...

Hi all,

I am a newbie who goes through tasks in apainfully slow and in an
inefficient way in Access. I've been experimenting here and there and
have figured out a few things, but for the most part, I still need
help. Here's a quick question that can help me get rolling with a new
project. I have multiple tables, all have the same field names and
formats...what is the quickest way to merge them all into one large
table? I've been doing an Append Query one by one, but that takes a
while, given that i have 45 tables. The table I want all the other
tables "merged" or appended to is tblAllStocks. Is there a faster way
to do this instead of Appening one table at a time?

Thank you for your help in advance,
-Haas

Well you could try using some code along the lines of

Dim db As DAO.Database, strSQL as String
Set db = CurrentDb

strSQL = Insert into tblAllStocks ( MyField1, MyField2 ...etc ) Select
MyOtherField1, MyOtherField2 ... etc from tblMyOtherTable

db.Execute strSQL, dbFailOnError
Set db = Nothing

substituting field names as appropriate and changing "tblMyOtherTable" each
time you run it.

Keith.
www.keithwilby.co.uk