Access查询空集如何赋值为0？

还是老套的办法

1、先合并数据

TRANSFORM Sum(a.收支合计) AS 收支合计之合计
SELECT a.职务
FROM (SELECT Sum(收入.收入) AS 收支合计, "收入" as 收支类型,收入.职务
FROM 收入
GROUP BY 收入.职务
UNION ALL SELECT Sum(支出.支出) AS 收支合计,"支出" as 收支类型,支出.职务
FROM 支出
GROUP BY 支出.职务
)  AS a
GROUP BY a.职务
PIVOT a.收支类型;

SELECT 收支合并.职务, 收支合并.收入, 收支合并.支出, Nz([收入])-Nz([支出]) AS 收支合计, Count(收支合并.职务) AS 合计计数
FROM 收支合并
GROUP BY 收支合并.职务, 收支合并.收入, 收支合并.支出
HAVING (((收支合并.职务)="教授"));