ASP.NET, SQL Server, WPF, C#, VB.NET

There are a couple of ways you can do it. 
 
You could simply create 6 different insert queries via the query GUI to 
 
insert data from the small tables to the big one.  But since this is in a 
 
"modules" database, I'm assuming that you're not trying to do this through 
 
the GUI.  So try the following (either typing it into the SQL window of a 
 
query or using CurrentDatabase().Execute [for MDB's] or 
 
CurrentProject.Connection.Execute [for ADP's]): 
 
INSERT INTO MyBigTable (Object, [Name], Desc, CodeNo) 
 
SELECT Object, [Name], Desc, CodeNo FROM MySmallTable1 UNION 
 
SELECT Object, [Name], Desc, CodeNo FROM MySmallTable2 UNION 
 
SELECT Object, [Name], Desc, CodeNo FROM MySmallTable3 UNION 
 
SELECT Object, [Name], Desc, CodeNo FROM MySmallTable4 UNION 
 
SELECT Object, [Name], Desc, CodeNo FROM MySmallTable5 UNION 
 
SELECT Object, [Name], Desc, CodeNo FROM MySmallTable6 
 
Note that this method will drop any duplicate rows between the 6 tables.  If 
 
you want to retain duplicates, change the all of the UNION statements to 
 
UNION ALL. 
 
Technically, you can drop the field names entirely from the INSERT and 
 
SELECT clauses (and just use SELECT *), but that presumes that all your 
 
tables have exactly the same fields in exactly the same order, no more, no 
 
less...which is not always a safe assumption to make. 
 
Best of luck, 
 
Rob 
 
"Mark F." <replytogroup@news.com> wrote in message 
 
news:tCpAg.43901$Cn6.12646@tornado.texas.rr.com...

I have an Access 2000 database that contains six tables. They all 
 
contain the same fields (Object, Name, Desc, CodeNo). I want to copy all 
 
the data from each table into one big table in another database file 
 
(mdb). Is that possible? 
 
Thanks, 
 
Mark