如何计算 Access 2010 表中所有列中的所有 NULL 值? [英] How can I count all the NULL values in all columns in an Access 2010 table?

问题描述

我需要能够计算 Access 2010 表的所有列中的所有 NULL.我的意思是，在 30 列(字段)中，有大量包含部分数据的记录.

I need to be able to count all the NULLS in all columns of an Access 2010 table. what I mean by that specifically, is that of the 30 columns (fields) there are loads of records with partial data.

我想计算整个表中有多少是空的.

I want to count in the entire table how many are empty.

我阅读了该站点上标题为如何计算表中的所有 NULL 值"的文章，但该文章提到了 SQL，而且，我很抱歉承认对我来说太复杂了.

I read the article on this site titled, How to count all NULL values in table, but that referred to SQL and also, was, I am sorry to admit too complex for me.

有人知道更多线索吗?

:)

推荐答案

对于单个字段，您可以使用简单的查询.

For a single field, you could use a simple query.

SELECT Count(*) AS CountOfNulls
FROM MyTable
WHERE some_field Is Null;

如果您想为单个查询中的多个字段分别计算 Nulls，您可以执行以下操作:

If you want to count Nulls separately for multiple fields in a single query, you can do something like this:

SELECT
    Sum(IIf(some_field Is Null, 1, 0)) AS NullsIn_some_field,
    Sum(IIf(another_field Is Null, 1, 0)) AS NullsIn_another_field
FROM MyTable;

如果您想要所有 Null 的总数，而不是每列的计数，您可以使用前面的查询作为子查询，并将各个列的计数相加.

If you want a grand total of all Nulls, instead of a count per column, you can use the previous query as a subquery and add up the individual column counts.

SELECT base.NullsIn_some_field + base.NullsIn_another_field AS total_nulls
FROM
    (
        SELECT
            Sum(IIf(some_field Is Null, 1, 0)) AS NullsIn_some_field,
            Sum(IIf(another_field Is Null, 1, 0)) AS NullsIn_another_field
        FROM MyTable
    ) AS base;

OTOH，如果您希望完全避免使用 SQL，或者只是觉得这些语句过于复杂，则不需要使用 SQL.您可以在 VBA 过程中使用 DCount() 函数.

OTOH, if you prefer to avoid SQL altogether, or simply find those statements too complex, you don't need to use SQL. You could use the DCount() function in a VBA procedure.

在立即窗口中使用数据库中表的名称运行以下过程:

Run the procedure below in the Immediate window with the name of a table in your database:

HowManyNulls "YourTable"

您可以使用 CTRL+g 键盘快捷键转到立即窗口.

You can go to the Immediate window with the CTRL+g keyboard shortcut.

Public Sub HowManyNulls(ByVal pTable As String)
    Dim db As DAO.Database
    Dim fld As DAO.Field
    Dim tdf As DAO.TableDef
    Dim lngNulls As Long
    Dim lngTotal As Long

    Set db = CurrentDb
    Set tdf = db.TableDefs(pTable)
    For Each fld In tdf.Fields
        'lngNulls = DCount("*", pTable, fld.Name & " Is Null")
        ' accommodate field names which need bracketing ...
        lngNulls = DCount("*", pTable, "[" & fld.Name & "] Is Null")
        lngTotal = lngTotal + lngNulls
        Debug.Print fld.Name, lngNulls
    Next fld
    Debug.Print "Grand total", lngTotal
    Set fld = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Sub

如果这些建议都不令人满意，请修改您的问题以帮助我们更好地了解您的需求.

If none of these suggestions is satisfactory, please revise your question to help us better understand what you need.

这篇关于如何计算 Access 2010 表中所有列中的所有 NULL 值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！