How do I append multiple tables into 1 large table in Access?

[Guest]

I need to append data from several tables (30 linked excel tables) into one
large table. The fields are the same in each table, but contain different
data. I need to analyze this data in one table or query. I know I can set up
individual append queries for each table, but there has to be an easier way.

 

[Guest]

far as i know theres no mass appending from multiple tables tool. ive played
and tried to find some kind of query but no luck. best bet would be to bite
the bullet and go make all 30 appends, then a macro to run them all in one
click. you should only have to do the legwork once, and then the macro can do
it instantly from then on.

HTH

 

[John Vinson]

I need to append data from several tables (30 linked excel tables) into one
large table. The fields are the same in each table, but contain different
data. I need to analyze this data in one table or query. I know I can set up
individual append queries for each table, but there has to be an easier way.

A UNION query is useful in this case:

SELECT field1, field2, field3, ...
FROM Spreadsheet1
UNION ALL
SELECT field1, field2, field3, ...
FROM Spreadsheet2
UNION ALL
SELECT field1, field2, field3, ...
FROM Spreadsheet3
UNION ALL

<etc>

The SELECTS must all have the same number of fields of matching
datatypes; and you can base an Append query on the stored UNION query.

If you use UNION it will eliminate all duplicates (and therefore run
more slowly due to the need to filter, and might not work on linked
Excel spreadsheets at all) - but if you want all the data from all the
sheets, UNION ALL is your ticket.

John W. Vinson[MVP]

 

[Guest]

Thank you very much...It works!...Saved me some time.

 

[debanga]

I too have a problem regarding multiple tables. I have multiple tables and each table contains some fields that are present in all the tables, now I want to append all of them in a new table, plz help me...