Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Make Table or Append - many to one

Status
Not open for further replies.

breilly192

Technical User
Nov 19, 2002
16
US
I have 100 or so like tables I was able to import into an Access mdb from Excel files. Now, I need to copy all of these individual tables into one - fields are the same in each table. I guess I should have imported them from files into one table, but that is beside the point.

Can a DoCmd.TransferSpreadsheet or DoCmdCopyObject efficiently copy all the data tables into one new table via a Do While loop or is a SQL statement better? I would have thought an insert or append query but didn't know how to code to vary the source tables within the mdb.

Any help would be appreciated.
 
I believe a "Union Query" will join all your tables into one.
And from this query, you can create a new table, either using a "Make table" query, or using ADO or DAO.
My familiarity with SQL is not very broad, but, I believe this is the general idea.
Hope this helps! good luck!
 
First Off you do need to create a Union query which will take some time but you should just be able to copy and paster a whole lot and just change the table name. In a query you need to do this:

SELECT *
FROM [Table1]

UNION SELECT *
FROM [Tabel2]

UNION SELECT *
FROM [Tabel3]
..........

Adding as many UNION SELCT's as you need.

After you have created your Union Query, you can run an insert query. The SQL code will be as follows:

INSERT INTO [Your Main Table to store ALL records]
SELECT * FROM [Your Union Query];

Then run the query and as long as the tables structures are the same this should do it. Let me know if you need any more help.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top