Microsoft Access 2003 SQL Question

Question

I need to union several tables with the different structure in Microsoft Access.

For example I have table:

table1 (column_a,column_b,column_c),
table2 (column_a,column_c),
table3 (column_d)

SQL looks like follows:

SELECT table1 column_a,column_b,column_c, Null as column_d FROM Table1
UNION
SELECT table2 column_a,Null as column_b, column_c, Null as column_d FROM Table2
UNION
SELECT table3 column_a, Null as column_b, Null as column_c, Null as column_d 
FROM Table3;

But sometimes MS Access shows the error message about incompatible types.

I think this because of generated columns with null values in one SELECT have the type incompatible with corresponding not autogenerated columns in another SELECT

Is there way to specify type of the autogenerated column with null?

Answer 1

What about replacing the Null's with empty strings ('')?

e.g.,

SELECT col_a, col_b,       col_c,       '' as col_d FROM Table1
UNION
SELECT col_a, '' as col_b, col_c,       '' as col_d FROM Table2
UNION
SELECT col_a, '' as col_b, '' as col_c, '' as col_d FROM Table3;