CrossTab将空值转换为"0“

Question

我需要将CrossTab替换为零，这样当没有数据时，我的报告就会有正确的总数。

有人告诉我要将Nz()函数放在SQL中，但是很难找到将它放在哪里来将COUNT([Traffic Report Totals - Query].TrafficTypeName)更改为用0替换空值。

我在下面包含了完整的SQL：

PARAMETERS [Forms]![Report Date Range]![txtStart] DateTime, [Forms]![Report Date Range]![txtEnd] DateTime;
TRANSFORM Count([Traffic Report Totals - Query].TrafficTypeName) AS CountOfTrafficTypeName
SELECT [Traffic Report Totals - Query].CommunityGroup, [Traffic Report Totals - Query].Date
FROM [Traffic Report Totals - Query] LEFT JOIN [Contract Budget] ON [Traffic Report Totals - Query].CommunityGroup = [Contract Budget].CommunityGroup
GROUP BY [Traffic Report Totals - Query].CommunityGroup, [Traffic Report Totals - Query].Date
PIVOT [Traffic Report Totals - Query].TrafficTypeName In ("A-Initial Realtor Showing","A-Initial Visit","B-Be Back","C-Additional Contract Paperwork","C-Cancellation","C-Closing","C-Contract","C-Contract Deposit","C-Final Orientation","Completion","C-Pre-Contruction Meeting","C-Pre-Drywall Walk","C-Realtor Showing","C-WJH Showing","Email Followup","Email Inquiry","Realtor Call","Realtor Visit","Telephone Follow-Up","Telephone Inquiry","Warranty Call");

Answer 1

在您的示例“流量报告汇总-查询”中，您需要将数据来源的查询中的计数字段包装起来。转到查询的SQL，然后检查字段并将它们包装在Nz()函数中。