query 2 tables into 1 table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChasW - 11 Sep 2006 22:51 GMT

I want to create a new table with the contents of 2 other tables whose
design specs are the same.

What would be the syntax to do this?

Right now, to get 1 query's results into a table I do something like:
SELECT tbl.* INTO tblNEW FROM tbl;

but what about from 2 sources? Is there a way to use UNION ALL in the
above statement?

Regards,
Charles

Reply to this Message

Smartin - 11 Sep 2006 23:29 GMT

> I want to create a new table with the contents of 2 other tables whose
> design specs are the same.
[quoted text clipped - 9 lines]
> Regards,
> Charles

Sure,

SELECT T3.* INTO tblNEW
FROM
(SELECT * FROM TABLE1 AS T1
UNION ALL
SELECT * FROM TABLE2 AS T2) AS T3;

HTH

Signature

Smartin

Reply to this Message

ChasW - 12 Sep 2006 05:03 GMT

>> I want to create a new table with the contents of 2 other tables whose
>> design specs are the same.
[quoted text clipped - 19 lines]
>
>HTH

This works in Access 2000, but in Access 97 I am getting "Syntax Error
in FROM clause"

How do I go about doing this for Access 97?

Reply to this Message

Smartin - 12 Sep 2006 17:33 GMT

>>> I want to create a new table with the contents of 2 other tables whose
>>> design specs are the same.
[quoted text clipped - 23 lines]
>
> How do I go about doing this for Access 97?

Hmm. I don't have access to A97 at the moment. Hopefully someone else
knows? Otherwise I will take a look next week.

Signature

Smartin

Reply to this Message

Smartin - 19 Sep 2006 00:41 GMT

>>> I want to create a new table with the contents of 2 other tables whose
>>> design specs are the same.
[quoted text clipped - 23 lines]
>
> How do I go about doing this for Access 97?

It appears the work-around in A97 is to put the subselect in a separate
query ...

QUERY GET_ALL_TABLES
====================
SELECT * FROM TABLE1
UNION ALL
SELECT * FROM TABLE2 ;

... And call it from the make-table query...

QUERY MAKE_NEW_TABLE
====================
SELECT * INTO NEWTABLE
FROM GET_ALL_TABLES;

Signature

Smartin

Reply to this Message