Combine Tables With Similar Field

[Guest]

I have several small tables and I would like to combine them to make one
large table. The field types are identical in each table. How would I combine
the tables?

 

[Roger Carlson]

With a Union query. You can't do it in the query builder. Essentially, you
do this:

Select * from Table1
UNION ALL
Select * from Table2
UNION ALL
Select... (and so forth)

as long as the matching fields are in the same order, this will work. If
they're not (or there are additional fields in some tables) you have to list
the fields you want (separated by commas) where the * is.

If you want to actually make a new table, save the above query (qryMyUnion)
and use it as the table in another query:

SELECT * INTO NewTable FROM qryMyUnion;

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Append query.

 

[Guest]

Both Roger's and Karl's methods will work. I suggest that if you use one of
them make sure that the new table has an autonumber field so that you have
something to tell the records apart. Actually with Roger's UNION query
example, there should not be any exact duplicates.
You might also what some kind of marker so you can tell from which table the
data originally came.