如何用零替换交叉表查询中的空值? [英] How do you replace nulls in a crosstab query with zeroes?
问题描述
基于Access中的以下SQL...
Based on the following SQL in Access...
TRANSFORM Sum([Shape_Length]/5280) AS MILES
SELECT "ONSHORE" AS Type, Sum(qry_CurYrTrans.Miles) AS [Total Of Miles]
FROM qry_CurYrTrans
GROUP BY "ONSHORE"
PIVOT qry_CurYrTrans.QComb IN ('1_HCA_PT','2_HCA_PT','3_HCA_PT','4_HCA_PT');
...我的结果返回了以下数据表:
... my results returned the following datasheet:
| Type | Total Of Miles | 1_HCA_PT | 2_HCA_PT | 3_HCA_PT | 4_HCA_PT |
| ONSHORE | 31.38 | | 0.30 | 7.80 | |
这个结果正是我想要的除了我想在空的单元格中看到零.
This result is exactly what I want except I want to see zeroes in the cells that are null.
这样做有哪些选择?如果可能的话,我想避免使用子查询.我还希望查询在 Access 的设计视图中保持可编辑状态.
What are some options for doing this? If possible, I'd like to avoid using a subquery. I'd also prefer the query to remain editable in Access' Design View.
推荐答案
我认为你必须使用 Nz 函数,它允许你将 NULL 转换为另一个值.在这种情况下,我使用函数的(可选)部分来表示,如果 Sum([Shape_Length]/5280) 为 NULL,则将其设置为 0".您可能必须在 0 周围使用引号,我不记得了.
I think you have to use the Nz function, which will allow you to convert NULLs to another value. In this case, I used the (optional) part of the function to say, "If Sum([Shape_Length]/5280) is NULL, set it to 0". You may have to use quotes around the 0, I can't recall.
TRANSFORM Nz(Sum([Shape_Length]/5280), 0) AS MILES
SELECT "ONSHORE" AS Type, Sum(qry_CurYrTrans.Miles) AS [Total Of Miles]
FROM qry_CurYrTrans
GROUP BY "ONSHORE"
PIVOT qry_CurYrTrans.QComb IN ('1_HCA_PT','2_HCA_PT','3_HCA_PT','4_HCA_PT');
这篇关于如何用零替换交叉表查询中的空值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!