[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.