Can you do this in a UNION QUERY?

[Guest]

I have three seprate queries I want to join into one. Can you do this with a
union query. I read that all fields must be identical. Does this mean I cant
have different fields in each query. Some are identical but not all. Most
have the same primary key and all have expressions, grouping, sorting etc.
Would I still be able to do a union query based off these queries? If not
what is another means to join these 3 queries together? All of them have
different results.

 

[John Spencer]

In a union query each of the "component" queries must return the same number
of fields and the fields should be of comparable types.

SELECT DOUBLENumberField, 30CharTextField, DateField
FROM SomeTable
UNION
SELECT LongNumberField, 50CharTextField, DateField
FROM SomeOtherTable

That should work

The query below will fail due to the mismatch of the field types and due to
3 fields in first component query and 4 fields in the second component
field.
SELECT DOUBLENumberField, 30CharTextField, DateField
FROM SomeTable
UNION
SELECT TextField, 50CharTextField, DateField, AnotherTextField
FROM SomeOtherTable

You can work around the number of fields by including a calculated field for
any missing fields or inserting a null as a place holder.
SELECT DOUBLENumberField, 30CharTextField, DateField, Field4
FROM SomeTable
UNION
SELECT LongNumberField, 50CharTextField, DateField, Null
FROM SomeOtherTable