Access MVP (2010-2015)

oOo.oOo.oOo.oOo.oOo.oOo

UNION Queries

A UNION query takes data from two (or more) tables and/or queries and makes it appear as if it is from one source, i.e…

SELECT FieldName, FieldName
FROM 1stTableOrQueryName
UNION SELECT FieldName, FieldName
FROM 2ndTableOrQueryName
UNION SELECT FieldName, FieldName
FROM 3ndTableOrQueryName;

A few points…

1. UNION queries are READ ONLY, no edits allowed!

2. In the above rendition there can be no duplication of the rows returned. If any duplicate rows are specifically required to be returned, a UNION ALL operation should be used.  However, I rarely find a reason to want duplicate rows in a UNION query.

3. These queries can *run* slowly so use them sparingly.

4. The Column Count must be the same for each query.  If they do not match up you can use Null in place of a field name as a placeholder, i.e…

SELECT FieldName, FieldName
FROM 1stTableOrQueryName
UNION SELECT FieldName, Null
FROM 2ndTableOrQueryName
UNION SELECT Null, FieldName
FROM 3ndTableOrQueryName;

5. You can specify Column Names

Specify Column Names

Specify Column Names

SELECT FieldName AS FirstName, FieldName AS LastName, FieldName AS TypeID
FROM 1stTableOrQueryName
UNION SELECT FieldName, FieldName, FieldName
FROM 2ndTableOrQueryName
UNION SELECT FieldName, FieldName, FieldName
FROM 3ndTableOrQueryName;

You only need to do this for the first query as the following queries will adopt whatever Column Name(s) is specified in the first query.

6. You can use ORDER BY in a UNION query, i.e…

ORDER BY LastName

ORDER BY LastName

SELECT cpFirstName, cpLastName
FROM 1stTableOrQueryName
UNION SELECT FieldName, FieldName
FROM 2ndTableOrQueryName
UNION SELECT FieldName, FieldName
FROM 3ndTableOrQueryName
ORDER BY cpLastName;

When specifying the ORDER BY you must use the FieldName from the first query as in the subsequent Tables and/or Queries the Field Names may not match or exist.

7. You can use Placeholders to *add* additional information (columns) to your query as shown in (5.), i.e…

For numeric, because the Field will default to TEXT you must declare it as NUMERIC…

SELECT FieldName, FieldName, Val(1) AS TypeID
FROM 1stTableOrQueryName
UNION SELECT FieldName, FieldName, Val(2)
FROM 2ndTableOrQueryName
UNION SELECT FieldName, FieldName, Val(3)
FROM 3ndTableOrQueryName;

…or for TEXT just type as usual…

SELECT FieldName, FieldName, “M” AS ItemID
FROM 1stTableOrQueryName
UNION SELECT FieldName, FieldName, “C”
FROM 2ndTableOrQueryName
UNION SELECT FieldName, FieldName, “S”
FROM 3ndTableOrQueryName;

Now, when you run your SELECT query you can filter on the Placeholder.

8. You can use IIf() Statements in your UNION query, however, because this type of query can run slowly, use sparingly…

SELECT FieldName, FieldName, Val(1)
FROM 1stTableOrQueryName
UNION SELECT FieldName, FieldName, Val(2)
FROM 2ndTableOrQueryName
UNION SELECT FieldName, FieldName, IIf([cpChecked]=0,Val(3)
FROM 3ndTableOrQueryName;

You’ll notice the Field *cpChecked* is not in the query itself.  It does not have to be, it only needs to be in the Table or Query used.

 810 total views,  1 views today

Comments are closed.