如何在MS-Access的计数查询中为空值返回零值? [英] How to Return a value of Zero for null Value in Count Query in MS-Access?

问题描述

我尝试在MS-Access的查询中检索两个打开类型.如下图所示.当我的结果中同时出现这两个值时，它们将产生每个值中多少个职位的数量.

I have two Opening Types that I am trying to retrieve counts for in my query in MS-Access. As seen in the photo below. When either both of those values are present in my results they will produce the quantity of how many position are in for each.

在相关表上根本没有选择任何一种开门类型时，它不会显示在查询计数上，如下所示.

When one of the opening types has not been selected at all on the respected table it does not show on the query count as seen below.

我的SQL如下

SELECT tblOpening.fk_OpeningTypeId
    ,Count(tblOpening.Position) AS CountOfPosition
FROM tblOpeningCity
INNER JOIN tblOpening ON tblOpeningCity.OpeningCityID = tblOpening.City
WHERE (
        ((tblOpening.Position) = "Flex Officer")
        AND ((tblOpening.Closed) = No)
        AND (
            (tblOpeningCity.OpeningCity) = "Livermore"
            OR (tblOpeningCity.OpeningCity) = "Pleasanton"
            )
        )
GROUP BY tblOpening.fk_OpeningTypeId;

我尝试更改联接，但没有运气.任何帮助将不胜感激.

I have tried changing the join and no luck. Any Help would be appreciated.

有些示例数据与我的数据库使用的数据相似.

Here is some sample data that is similar to what my database would use.

https://drive.google.com/open?id=1X1W-wctcP7SiYIOWx4VYTOh03zddF81r

推荐答案

您可以使用LEFT JOIN完成所需的操作. MS Access使合并不同条件变得困难，因此这可能满足您的要求:

You can accomplish what you want using a LEFT JOIN. MS Access makes it hard to incorporate the difference conditions, so this probably does what you want:

SELECT o.fk_OpeningTypeId, Count(oc.OpeningCityID) AS CountOfPosition
FROM tblOpening as o LEFT JOIN
     (SELECT oc.*
      FROM tblOpeningCity oc
      WHERE oc.OpeningCity IN ("Livermore", "Pleasanton")
     ) as oc
     ON oc.OpeningCityID = o.City
WHERE o.Position = "Flex Officer" AND
      o.Closed = No
GROUP BY o.fk_OpeningTypeId;

o上的筛选器可能正在删除您想要的内容.如果是这样，那么条件聚合将解决此问题:

It is possible that the filters on o are removing what you want. If so, then conditional aggregation will fix that:

SELECT o.fk_OpeningTypeId, 
       SUM(IIF(oc.OpeningCityID IS NOT NULL AND
               o.Position = "Flex Officer" AND
               o.Closed = No, 1, 0)
          ) AS CountOfPosition
FROM tblOpening as o LEFT JOIN
     (SELECT oc.*
      FROM tblOpeningCity oc
      WHERE oc.OpeningCity IN ("Livermore", "Pleasanton")
     ) as oc
     ON oc.OpeningCityID = o.City
GROUP BY o.fk_OpeningTypeId;

访问数据库示例

这篇关于如何在MS-Access的计数查询中为空值返回零值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！