From: "John Beamish" 

SELECT *
FROM [select f1 from table1 union select f2 from table2]. AS mystuff;

"Adam Flinton"  wrote in message
news:3ebb74d1{at}w3.nls.net...
> Why didn't union/union all followed by an orderby statement work?
>
> I'm sure access has union/union all & order by (nb last used Access:
> version = 97 in about 1999/2k)
>
> Union is for doing just what you've set out below.
>
> Adam
>
>
> Chris Robinson wrote:
> > hehe - yeh, I might as well give an example anyways (just incase someone
> > here knows of a more efficient way to do it).  Take tables A and B
> > below, each table contains the same kind of information (i.e. a field
> > called emp_no).
> >
> > *Table A - Finance         Table B - Sales*
> >     emp_no                        emp_no
> >       0001                            0004
> >       0002                            0005
> >       0003                            0006
> >
> > Ideally I'd like a query that pulls the information from table 1 and
> > table 2 into one column.  So, the ideal result would be a list of all
> > employee numbers:
> >
> > *Query Result*
> >     emp_no
> >       0001
> >       0002
> >       0003
> >       0004
> >       0005
> >       0006
> >
> > It doesn't look to hard but selecting each field from each table just
> > gives you a result that has 2 columns - one contains the emp_no's from
> > table A and the other, the emp_no's from table B.
> >
>

--- BBBS/NT v4.01 Flag-4