combining hundreds of tables into a single table

[impulse24]

Hi,

Help ME.

I have received a single access database that has over 100 tables. It looks like the operator kept creating copies of the table and just changing the contents of the data. I need to combine the tables. How do I run a query against all the tables to append the data to a single table. I'm guessing I have to create a macro, but I don't know how to get a list of the tables. Any help please

 

[MazeWorX]

Why? one table.

If you really want it in one table why not export it to excel, edit what you want, import it back into access although I wouldn’t recommend one table. See the help file for Database design or one of the many articles on the web. Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[impulse24]

All that is in these tables is at most 50-60 records. Can you export multiple tables to an excel sheet at one time? How? I know you can select a table and export it, but i'm not about to select over 100 tables to create a spreadsheet. There has got to be some kind of append query that will combine the contents of multiple tables into a single table. It is a database that has had a new table created each week, instead of continually adding data to an existing table. The person would simply copy the table structure only, rename the original table with a time stamp, and then start entering new data into the new table. I need a cumulative database, not 100 different tables. Please help.

 

[Craig0201]

Write a simple routine to do it.

Something like

Dim tbl As TableDef

For Each tbl in CurrentDB.TableDefs
'Create your append query here on the fly
Next

Craig

 

[MazeWorX]

Try a "Make Table Query"

create a query in design view. add the tables you want to gather your data from. Add the fields. From the tool bar select Query/Make table query and follow the instructions, run the query.

Should work ok

hth Remember amateurs built the ark - professionals built the Titanic

[flush]

 

[MichaelRed]

I would deffinitly recommend Craig0201's advice. I would studiously avoid side trips through excel (particularly in this exercise).

Depending on the details, an "Update" query would work well, and not introduce as many duplications.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[impulse24]

How do you create an append query on the fly?

 

[MichaelRed]

write the sql "in code" and execute it through any of hte available methods, partially dependent on the version of Ms. A. you are using

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[PWise]

try this create 1 query
select * from table1
union select * from table2
union select * from table3
union select * from table4....;
save as qrymultitable
create a maketable query

put the qrymultitable in the grid and select the *
run the query and you will have a new table from all the old tables
i like adding a field with the source in a union query
to do that the code will be
select * ,'table1' as sourcefrom table1
union select * ,'table2' as source from table2
union select * ,'table3' as source from table3
union select * ,'table1' as source from table4....;