您如何用零替换交叉表查询中的空值?

本文介绍了您如何用零替换交叉表查询中的空值?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

基于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');