Microsoft Access 2003 SQL 问题

Question

我需要在 Microsoft Access 中合并多个具有不同结构的表。

例如，我有表：

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

SQL 如下所示：

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;

但有时 MS Access 显示有关不兼容类型的错误消息。

我认为这是因为一个 SELECT 中具有空值的生成列的类型与另一个 SELECT 中相应的非自动生成列不兼容

有没有办法指定带有 null 的自动生成列的类型？

Answer 1

用空字符串 ('') 替换 Null 怎么样？

例如，

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;