ms-access Access SQL Union (Merge) Queries

Example

When you wish to combine the results of multiple tables or queries with similar fields together into a single resulting data set without performing any relational joins (i.e. you want to list one dataset immediately after the other), you will use a UNION query. However, it is notable that these queries must be manually created in SQL View.

Syntax of a UNION query is

SELECT
  floatingpoint_field AS floatptfld,
  text_field
FROM first_table
  UNION
SELECT
  integer_field,
  decimal_field
FROM a_saved_query
  UNION
SELECT
  1.0,
  "hi there Jack"

and will return a two-field dataset with field (column) names: floatptfld and text_field

It is critical that the data types (and data styles) for subsequently merged tables fields are compatible with the first query in the series. In other words, if the first SELECT query generates a number for the first column, the second query must also return a number in the first column. In addition to matching types of fields in order, the SELECT statements must return the same number of fields. Names for the fields of the resulting datasheet are inherited from the first table definition.

The following query would NOT be legal, as text cannot be turned into decimal data nor can floating point numbers be converted to integers (without explicit truncation or rounding and type-casting).

SELECT
  integer_field AS this_really_wont_turn_out_well,
  decimal_field
FROM a_saved_query
  UNION
SELECT
  floatingpoint_field,
  text_field
FROM first_table