Single WHERE and ORDER clause in UNION?

[cjg.groups]

Hello, can I have a single WHERE clause and ORDER BY clause in a UNION
query? I could put a WHERE and ORDER BY after each of the SELECTs,
but that's messy.

I'm cleaning some poorly designed tables which stored four identical
groups of fields in each record. UNION ALL is used to make this one
group of fields with four times as many records.

Much of these records are blank, so I would like single "WHERE name IS
NOT NULL". UNION can't filter all of these as duplicate because of
some qualifiers, and UNION ALL is faster.

Also, I've used AS to rename the fields in the first SELECT statement,
since each group of four fields had slightly different names. Can I
use a WHERE and ORDER BY on those renamed field?

Thanks!!

 

[John Spencer (MVP)]

You will need to use a where cluase on each query in the union. Or at least

You can only use one ORDER BY clause in a union query. It is added to the
last query, BUT it uses the field names from the first query.

SELECT A, B, C as XX
FROM Table1
UNION ALL
SELECT D, E, F
FROM Table2
WHERE F is not Null
UNION ALL
SELECT G, H, I
FROM Table3
WHERE I > 25
ORDER BY A, XX, B

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer (MVP)]

I forgot to mention that you can use the Union query as the source or another
query (or even as a subquery)

SELECT A, B, XX
FROM TheSavedUnionQuery
WHERE XX >25
ORDER BY A, XX, B

Or you can use the union query as a subquery in the FROM clause and
SELECT A, B, XX
FROM
(SELECT A, B, C as XX
FROM Table1
UNION ALL
SELECT D, E, F
FROM Table2
UNION ALL
SELECT G, H, I
FROM Table3) as X
WHERE XX > 25
ORDER BY A, XX, B


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[cjg.groups]

Perfect, thanks and thanks! I strongly suspected I could put a SELECT
in the FROM clause, but Access Help didn't mention it anywhere. I
really wanted to keep it all in one query for compactness.

MVPs save the day, yet again!

 

[cjg.groups]

And to clarify the field names below:

SELECT A, B, ZZ
FROM
(SELECT A, B, C as XX
FROM Table1
UNION ALL
SELECT D, E, F
FROM Table2
UNION ALL
SELECT G, H, I
FROM Table3) as X
WHERE XX > 25
ORDER BY A, XX, B

I renamed XX in the nested SELECT and was able to use it in the WHERE
of the outer SELECT. I renamed the previous example's outer XX to ZZ
to demonstrate this.

Thanks again.