[AccessD] Access 2000 - Union query

Hi Dave

> Windows2000 Access 2000

> I'm trying to use "UNION" the first time.
> I have no problem as a select query, but
> I can't get it to work as an append query.

> All fiels in all tables are 50 chr strings. Appending
> either withour any UNION causes no problem.

> I can "work around" by using the UNION query
> as the source for an append query

> Any pointers on the following will be 
> appreciated.

> Dave

> ==========================================
> This works

> SELECT Table1.Field1, Table1.Field2
> FROM Table1

> UNION SELECT Table1.Field1, Table1.Field2
> FROM Table2;
> ==========================================
> This doesn't, access points to an error in
> the second select statement

> INSERT INTO Table12 ( Field1, Field2 )

> SELECT Table1.Field1, Table1.Field2
> FROM Table1

> UNION SELECT Table2.Field1, Table2.Field2
> FROM Table2;

You have at least two options.
Either - as you mention - move the select/union part to a separate
query and use that as source in the append query (this would be the
preferred non-fancy method); or wrap the select/union part as an
in-line (sub)query using the strange "[ .. ]." syntax which I learned
from Paul (look in the archives for subject: SQL in-line subquery):

  INSERT INTO Table12
    (Field1, Field2)
  SELECT
    Field1, Field2
  FROM
    [SELECT
      Field1, Field2
    FROM
      Table1
    UNION ALL
    SELECT
      Field1, Field2
    FROM
      Table2;].
    AS subQuery;

UNION ALL will select all records from the two tables.
Omit ALL if you wish distinct records only.
The name subQuery can be nearly anything except reserved words.

/gustav