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
