[AccessD] Access 2000 - Union query

Stuart

Thank you for the effort and the offer.

I hope that you've seen what Gustav's reply,
and that you can also benefit from it.

Dave

----- Original Message -----
From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
To: "dave sharpe" <davesharpe2 at cox.net>; <accessd at databaseadvisors.com>
Sent: Saturday, February 01, 2003 8:01 AM
Subject: Re: [AccessD] Access 2000 - Union query


>
> > I tried every variation that I could think of with no success.
> ....snip
>
> I just tried this myself.
> In A2K with an ADP to MSDE this works fine as a stored procedure.
>
> INSERT INTO  table2( f1 )
> (SELECT Max(f1)
> FROM table1
> union
> select min(f1) from table1)
>
>
> As a query in an A2K database it doesn't . Clearly it's a limitation in
the Jet
> implementation of SQL. The only way I managed to do it was using two
separate
> queries.
>
> Query2:
> SELECT Max(f1) as f2
> FROM table1
> UNION select min(f1)  as f2 from table1;
>
> Query1:
> INSERT INTO table2 ( f1 )
> SELECT [f2]
> FROM query2;
>
> When I tried this:
> INSERT INTO table2 ( f1 )
> (SELECT Max(f1) as f2
> FROM table1
> UNION select min(f1)  as f2 from table1)
>
> Access "spat the dummy" at the opening bracket.
>
> "Such is life"
>
>
> >
> > ----- Original Message -----
> > From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
> > To: "dave sharpe" <davesharpe2 at cox.net>; <accessd at databaseadvisors.com>
> > Sent: Friday, January 31, 2003 10:11 PM
> > Subject: Re: [AccessD] Access 2000 - Union query
> >
> >
> > > > 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;
> > > >
> > >
> > > Try using brackets to ensure that the Union is applied to the correct
> > part.
> > >
> > > INSERT INTO Table12 ( Field1, Field2 )
> > > (SELECT Table1.Field1, Table1.Field2
> > >  FROM Table1
> > >   UNION SELECT Table2.Field1, Table2.Field2
> > >  FROM Table2);
>
> --
> Lexacorp Ltd
> http://www.lexacorp.com.pg
> Information Technology Consultancy, Software Development,System Support.
>
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>