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:
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