How can I count all the NULL values in all columns

Question

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.

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.

Anyone got any futher clues?

:)

Answer 1

There is no easy way to perform this kind of count across "all columns" of a table when you do not know the column names in advance.

If you want to do so, you have to write a program to read the "metadata" of the database, extract a list of columns and tables from that data, for each column build a separate SQL statement, then execute that statement and add the number you get back to a running total of NULL values found.

Does this sound like something you might consider pursuing? If so, you should search for solutions on getting the list of tables in an MS Access database and getting the list of columns for a table in an Access database, and then combine that information with the information you already have from the question about counting NULLs in a column.

Answer 2

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

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

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;

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, 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"

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.

Answer 3

To count null values in a table of all columns:

select count(*) from(select a from tt where a is  null
              union all
            select b from tt   where b is  null
              union all
            select c from tt where c is  null)