Appending many tables to one table

[wdbouk]

Hello,

I have around 120 tables in my database. Monthly data for 10 years period . The tables have the same data type and number of columns(more than 20 columns). I want to combine these tables in 1 table. I am wondering what is the easiest way to do so.
I hope it is not by appending each table individually.
Best wishes

 

[jsteph]

if they have fields in identical positions (names need not be identical), then the following is quick and dirty

Make a query:
Select table1.* from table1
union
Select table2.* from table2
union
Select table3.* from table3
...blah, blah...

Save it, then drag into into a new Append query with the new destination table.
--Jim

 

[SkipVought]

I'd ADD just one field for TheDate starting with Jan 1992

Select table1.*, DateSerial(1992, 1, 1) from table1
union
Select table2.*, DateSerial(1992, 2, 1) from table2
union
Select table3.*, DateSerial(1992, 3, 1) from table3
...blah, blah...

Naturally, the structure of your table would need to be simply changed.

Skip,

_{[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large!}

 

[wdbouk]

I am receiving :
syntax error in query. incomplete query clause
My union query that I wrote have the following format but first I am creating a query where I select all the tables and after adding the tables I go to the sql specific , union and i write the following:

SELECT * FROM [Y0186]
UNION
SELECT * FROM [Y0187]
UNION
SELECT * FROM [Y0188]
UNION
SELECT * FROM [Y0189]
UNION
SELECT * FROM [Y0190]
UNION
SELECT * FROM [Y0191]
UNION
SELECT * FROM [Y0192]
UNION
SELECT * FROM [Y0193]
UNION
SELECT * FROM [Y0194]
UNION
SELECT * FROM [Y0195]
UNION
SELECT * FROM [Y0196]
UNION
SELECT * FROM [Y0197]
UNION

 

[lespaul]

Don't add all the tables to the query. Create a new query, switch to the Sql view, type your query, don't end with a UNION like your example above.

Leslie

 

[wdbouk]

I am receiving now that the query is too complex

 

[lespaul]

have you tried adding each new table one at a time to see if there is a problem with a particular table? With the structure of your union query, the structure of all your tables has to match exactly; could there be a table with one extra field or one missing field?

Leslie

 

[wdbouk]

there are 24 fields in each table.I checked all of them.
the field names were assigned by access when i imported the data from excel Field1, Field2 etc.. , so they must have the same name. I will see if I can add each table alone although as I said before I posted this thread to avoid time consuming and i just wanted to do them in one shot.
Thanks

 

[lespaul]

This isn't a time consuming answer, it's the easiest and simplest way to take the same information from multiple locations and combine it in a single location. Except for the extra UNION at the end of your query, there's nothing structurally wrong with what you wrote.

Leslie

 

[wdbouk]

I tried to run the union query using two tables and I started to add table by table.The query was working till the 9th table where it gave me :
Internal OLE automatic error. I wasn't able to proceed. Is this the problem? if not how to solve this one?
Thanks

 

[wdbouk]

Soory automation and not automatic

 

[wdbouk]

The problem is solved. The data type for one filed was not matching the datatype of the others.
A final question is how to save the union results in a table. I need to import this table to matlab and consequently I can't save the results in a query. I need a table.
Best wishes

 

[lespaul]

INSERT INTO NEWTABLENAME (put union query here)

Or I think one of the query choice is to transform an existing query to a maketable query and it will convert it for you.

glad you got it figured out.

leslie

 

[wdbouk]

Hi again Leslie,
unfortunately I wasn't able to save the union query into a table. Assume I have the following query and I want to save the result in a table called XYZ. How can I do it exactly? Insert into newtablename was giving me a syntax error. I think i was missing something.

SELECT * FROM [Y0185]
UNION
SELECT * FROM [Y0186]
UNION

 

[wdbouk]

of course ignore the last union (typing mistake)

 

[lespaul]

have you created the new table to insert into? I think there's another command needed if the table doesn't exist. However, I'm pretty sure if you have the query open you can select a 'Make Table' query and it will convert it for you.

leslie

 

[wdbouk]

sorry for bothering you but I couldn't find it. If I click on the make table query the union query disappears and a new query is created.

 

[lespaul]

that is what it's suppose to do, change your existing query to a make table query.

what is the new query that is created?

Leslie

 

[wdbouk]

I managed to solve it. I created a new query where I appended the union query to a new table
I hope i didn't bother you today
Thanks a lot