Combining Multiple Tables

[tachyon17]

I have a bunch of Tables like 30 or so. They all have the same fields
and no primary key. What I want to do is just combine them into one
large table. I'm using Access 97. By Combine I mean just have a huge
table with the contents of all of them, don't really want to deal with
duplicates or anything. Essentially I just want to do an Add of all
the records from all my tables into a new table
Thanks

 

[John W. Vinson]

I have a bunch of Tables like 30 or so. They all have the same fields
and no primary key. What I want to do is just combine them into one
large table. I'm using Access 97. By Combine I mean just have a huge
table with the contents of all of them, don't really want to deal with
duplicates or anything. Essentially I just want to do an Add of all
the records from all my tables into a new table
Thanks

Then you'll need to either run 30 Append queries; or create a UNION query and
append it.

To create the UNION query, first just use the query grid to create a simple,
no-criteria select query selecting all the fields in one of your tables. Then
go to SQL view (View... SQL on the menu or use the SQL option on the leftmost
tool in the toolbar). You'll see something like

SELECT this, that, theother, who, what, IDontKnow FROM Table1;

Edit this to

SELECT this, that, theother, who, what, IDontKnow FROM Table1
UNION ALL
SELECT this, that, theother, who, what, IDontKnow FROM Table2
UNION ALL
SELECT this, that, theother, who, what, IDontKnow FROM Table3
UNION ALL
<etc etc etc>

through all 30 tables. If you say UNION instead of UNION ALL Access will get
rid of all the duplicates.

Save this query as uniAllTables and then create an Append query based on
uniAllTables appending to your target table.

John W. Vinson [MVP]