query problem

[brino]

hi all !

i needed to append about 20 fields from a query into the 1 field of
another table so that i can produce a report. i tried to do this in an
append query but it would not work because i was appending all of the
fields to the 1 field in the destination table. is there a way of doing

this with the 1 query. at present i have to do over 20 querys , each
one appending a single field only.
im not sure if its possible to do this in one query ??? it would save a

lot of work and speed up the database also.


thanks
brino

 

[Brendan Reynolds]

You can do it with two queries. First, a UNION query to bring the 20 columns
into one, e.g. ...

SELECT tblSource.Source1 AS Source FROM tblSource
UNION ALL SELECT tblSource.Source2 FROM tblSource
UNION ALL SELECT tblSource.Source3 FROM tblSource
ORDER BY Source;

.... where 'Source1', 'Source2', 'Source3' etc. are the columns from which
you want to append.

You can't create a UNION query in query design view, so you'll have to
switch to SQL view to do that.

Now append from the UNION query to the target table ...

INSERT INTO tblTarget ( Target )
SELECT qryUnion.Source
FROM qryUnion;

.... where 'qryUnion' is the name of the saved UNION query, and 'Target' is
the name of the column to which you want to append.

The fact that you need to append 20 columns into one probably indicates a
flaw in the database design, but of course that may be something beyond your
control.