[AccessD] Access 2000 - Union query

Gustav - you hit it write on the head. 

Thank you for the info I never would have
gotten there without someone pointing the
way.

Dave


----- Original Message ----- 
From: "Gustav Brock" <gustav at cactus.dk>
To: "dave sharpe" <accessd at databaseadvisors.com>
Sent: Saturday, February 01, 2003 6:30 AM
Subject: Re: [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
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>