非NULL、非空データ件数を一括集計するSQLをVBAで生成する

Posted at — Sep 3, 2025

概要

データの整合性や品質は、開発するシステムの生命線です。特に、大規模なデータ移行や外部システムとの連携プロジェクトでは、「このテーブルのこの列、どれくらいデータが埋まっているんだっけ？」といった確認作業が頻繁に発生します。このような場合、SQLのSELECT COUNT(*)を使って非NULL・非空データの件数を調べたりします。しかし、テーブル内には何十、何百と列が存在し、これらに対して調査のために一つひとつSELECT COUNT(*) を実行するのは、正直なところ骨が折れる作業です。というか、手作業でSQLを書くのはミスの元ですし、時間もかかります。なんなら、そもそもやりたくない＿(　_´ω`)_ﾍﾟｼｮ

そこで今回は、そんな地道なデータ品質チェックを効率化するための、Access MDBで指定されたテーブルの全列に対して非NULL・非空データの件数を集計するSQLを動的に生成・実行するVBAスクリプトを作成しました。

背景

このスクリプトが生まれた背景には、あるデータクレンジングプロジェクトでの切実な課題がありました。基幹システムから連携された数十のテーブルには、残念ながら多くの「データの抜け漏れ」や「不正確なデータ」が存在していました。

プロジェクトの初期段階でこの課題に気づいていたため、「どのテーブルの、どの列に、どれくらいの有効データが存在するのか」という現状を網羅的に把握することとしました。この「有効データ」の定義は、列のデータ型によって異なります。

テキスト型なら、NULLだけでなく空文字やスペースのみのデータも「無効」としたい。
数値型の場合、NULLは無効だが、0は有効なデータとして扱いたいケースもあれば、0も「未入力」と見なしたいケースもある。
日付型やブール型は、シンプルにNULLでないことを確認したい。

これらの要件を満たすSQLを各列に対して手作業で記述するのは、時間がかかるだけでなく、ヒューマンエラーの温床にもなります。この定型的かつ重要な作業を自動化し、誰でも迅速かつ正確にデータ品質の概況を把握できるようにする。それが、このスクリプトを開発した最大の目的です。とはいえ、背景としてはまぁまぁヘビーな話ですが、コード自体は簡単に使えるようシンプルで汎用性の高いコードを心がけました。

コード

実際に使用するVBAコードは以下の通りです。

Option Compare Database

'このVBAスクリプトは、Access MDBで指定されたテーブルの全列に対して非NULL・非空データの件数を集計するSQLを動的に生成・実行します。

Sub GenerateAndExecuteColumnCountQuery(tableName As String)
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim sql As String
    Dim selectClause As String
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb()
    Set tdf = db.TableDefs(tableName)
    
    ' SELECT句を動的に構築
    selectClause = "SELECT " & vbCrLf
    
    For Each fld In tdf.Fields
        If selectClause <> "SELECT " & vbCrLf Then
            selectClause = selectClause & "," & vbCrLf
        End If
        
        ' データ型に応じて条件を分岐
        Select Case fld.Type
            Case dbText, dbMemo
                ' テキスト型：NULL、空文字、スペースのみを除外
                selectClause = selectClause & "    SUM(IIf([" & fld.Name & "] IS NOT NULL AND Trim([" & fld.Name & "]) <> '', 1, 0)) AS [" & fld.Name & "_Count]"
'            Case dbByte, dbInteger, dbLong, dbSingle, dbDouble, dbCurrency, dbDecimal
'                ' 数値型：NULLのみ除外（0は集計対象）
'                selectClause = selectClause & "    SUM(IIf([" & fld.Name & "] IS NOT NULL, 1, 0)) AS [" & fld.Name & "_Count]"
            Case dbByte, dbInteger, dbLong, dbSingle, dbDouble, dbCurrency, dbDecimal
                 ' 数値型：NULL、0を除外
                 selectClause = selectClause & "    SUM(IIf([" & fld.Name & "] IS NOT NULL AND [" & fld.Name & "] <> 0, 1, 0)) AS [" & fld.Name & "_Count]"
            Case dbDate
                ' 日付型：NULLのみ除外
                selectClause = selectClause & "    SUM(IIf([" & fld.Name & "] IS NOT NULL, 1, 0)) AS [" & fld.Name & "_Count]"
            Case dbBoolean
                ' ブール型：NULLのみ除外
                selectClause = selectClause & "    SUM(IIf([" & fld.Name & "] IS NOT NULL, 1, 0)) AS [" & fld.Name & "_Count]"
            Case Else
                ' その他の型：NULLのみ除外
                selectClause = selectClause & "    SUM(IIf([" & fld.Name & "] IS NOT NULL, 1, 0)) AS [" & fld.Name & "_Count]"
        End Select
    Next fld
    
    ' 完全なSQL文を構築
    sql = selectClause & vbCrLf & "FROM [" & tableName & "]"
    
    ' SQLをイミディエイトウィンドウに出力
    Debug.Print "=== 生成されたSQL ==="
    Debug.Print sql
    Debug.Print "==================="
    
    ' SQLを実行して結果を表示
    Set rs = db.OpenRecordset(sql)
    
    If Not rs.EOF Then
        Debug.Print "=== 実行結果 ==="
        For i = 0 To rs.Fields.count - 1
            Debug.Print rs.Fields(i).Name & ": " & rs.Fields(i).Value
        Next i
        Debug.Print "==============="
    End If
    
    ' リソースを解放
    rs.Close
    Set rs = Nothing
    Set tdf = Nothing
    Set db = Nothing
    
    MsgBox "処理が完了しました。イミディエイトウィンドウ（Ctrl+G）で結果を確認してください。"
End Sub

' 生成されたSQLのみを取得する関数
Function GetColumnCountSQL(tableName As String) As String
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim selectClause As String
    
    Set db = CurrentDb()
    Set tdf = db.TableDefs(tableName)
    
    selectClause = "SELECT " & vbCrLf
    
    For Each fld In tdf.Fields
        If selectClause <> "SELECT " & vbCrLf Then
            selectClause = selectClause & "," & vbCrLf
        End If
        
        Select Case fld.Type
            Case dbText, dbMemo
                selectClause = selectClause & "    SUM(IIf([" & fld.Name & "] IS NOT NULL AND Trim([" & fld.Name & "]) <> '', 1, 0)) AS [" & fld.Name & "_Count]"
            Case dbByte, dbInteger, dbLong, dbSingle, dbDouble, dbCurrency, dbDecimal
                selectClause = selectClause & "    SUM(IIf([" & fld.Name & "] IS NOT NULL, 1, 0)) AS [" & fld.Name & "_Count]"
            Case dbDate
                selectClause = selectClause & "    SUM(IIf([" & fld.Name & "] IS NOT NULL, 1, 0)) AS [" & fld.Name & "_Count]"
            Case dbBoolean
                selectClause = selectClause & "    SUM(IIf([" & fld.Name & "] IS NOT NULL, 1, 0)) AS [" & fld.Name & "_Count]"
            Case Else
                selectClause = selectClause & "    SUM(IIf([" & fld.Name & "] IS NOT NULL, 1, 0)) AS [" & fld.Name & "_Count]"
        End Select
    Next fld
    
    GetColumnCountSQL = selectClause & vbCrLf & "FROM [" & tableName & "]"
    
    Set tdf = Nothing
    Set db = Nothing
End Function

コード解説

このVBAスクリプトは、大きく分けて2つのコンポーネントで構成されています。

GenerateAndExecuteColumnCountQuery: SQLの生成と実行を両方行うメインのサブルーチン。
GetColumnCountSQL: SQLの生成のみを行う関数（他の処理への組み込みを想定）。

テーブル定義の動的取得

このスクリプトは、DAO (Data Access Objects) を使ってテーブルの定義情報にアクセスしています。

Set db = CurrentDb()
Set tdf = db.TableDefs(tableName)

For Each fld In tdf.Fields
    ' ... フィールド（列）ごとの処理 ...
Next fld

このループにより、テーブルの全列情報を一つずつ取得し、動的にSQLを組み立てていきます。これにより、テーブルの仕様変更にも強く、汎用性の高い処理が実現できています。

データ型に応じた最適なカウント処理

このスクリプトの最も重要な部分は、Select Case を使ってフィールドのデータ型 (fld.Type) ごとに最適な集計ロジックを適用している点です。

Select Case fld.Type
    Case dbText, dbMemo
        ' テキスト型：NULL、空文字、スペースのみを除外
        selectClause = selectClause & "   SUM(IIf([" & fld.Name & "] IS NOT NULL AND Trim([" & fld.Name & "]) <> '', 1, 0)) AS [" & fld.Name & "_Count]"
    Case dbByte, dbInteger, dbLong, dbSingle, dbDouble, dbCurrency, dbDecimal
        ' 数値型：NULL、0を除外
        selectClause = selectClause & "   SUM(IIf([" & fld.Name & "] IS NOT NULL AND [" & fld.Name & "] <> 0, 1, 0)) AS [" & fld.Name & "_Count]"
    Case Else
        ' その他の型：NULLのみ除外
        selectClause = selectClause & "   SUM(IIf([" & fld.Name & "] IS NOT NULL, 1, 0)) AS [" & fld.Name & "_Count]"
End Select

テキスト型 (dbText, dbMemo): IS NOT NULL に加え、Trim() 関数で前後のスペースを削除した結果が空文字 ('') でないこともチェックしています。これにより、スペースだけの無意味なデータを除外できます。
数値型 (各種): NULL だけでなく 0 もカウントから除外しています。これは、システムによっては未入力時のデフォルト値として 0 が設定されるケースを想定したものです。もし 0 を有効なデータとしてカウントしたい場合は、AND [" & fld.Name & "] <> 0 の部分を削除すれば容易にカスタマイズ可能です。
その他の型: 基本的に NULL でないことを確認します。

この SUM(IIf(条件, 1, 0)) という書き方は、Access SQLにおける条件付き集計の常套手段です。条件に合致すれば 1 を、そうでなければ 0 を返し、その合計を SUM で求めることで、実質的な COUNT を実現しています。

スクリプトの使用方法

使い方は非常に簡単です。

AccessのVBAエディタ（Alt + F11）を開き、標準モジュールに上記のコードを貼り付けます。このとき、標準モジュールはわかりやすい名前（例：Module_ColumnCountなど）で作成し保存することをオススメします。
イミディエイトウィンドウ（Ctrl + G）を開きます。
イミディエイトウィンドウに以下のように入力し、Enterキーを押します。（"hoge_table" の部分を実際のテーブル名に置き換えてください）
```
GenerateAndExecuteColumnCountQuery "hoge_table"
```
処理が完了するとメッセージボックスが表示されます。イミディエイトウィンドウに、生成されたSQL文と、その実行結果（各列の非NULL/非空データ件数）が出力されます。

=== 生成されたSQL ===
SELECT 
    SUM(IIf([ID] IS NOT NULL AND [ID] <> 0, 1, 0)) AS [ID_Count],
    SUM(IIf([ProductName] IS NOT NULL AND Trim([ProductName]) <> '', 1, 0)) AS [ProductName_Count],
    SUM(IIf([UnitPrice] IS NOT NULL AND [UnitPrice] <> 0, 1, 0)) AS [UnitPrice_Count],
    SUM(IIf([OrderDate] IS NOT NULL, 1, 0)) AS [OrderDate_Count]
FROM [商品リスト]
===================
=== 実行結果 ===
ID_Count: 150
ProductName_Count: 148
UnitPrice_Count: 135
OrderDate_Count: 150
===============

この結果を見れば、「ID_Count列が150件なのに対して同じ結果でない列が存在している（ProductName列に2件、UnitPrice列に15件のデータ抜け漏れがある）」といったことがわかります。

今後の展望

このスクリプトは、データ品質管理の第一歩としてシンプルな実装にしてありますが、さらに発展させることでより高度なデータ分析をしたり、運用に役立てたりすることが可能です。以下はその一例です。

結果のテーブル/CSV出力: イミディエイトウィンドウへの出力だけでなく、集計結果を日時と共にログテーブルに保存したり、CSVファイルに出力したりすることで、データ品質の定点観測が可能になります。
GUIの導入: Accessフォームを作成し、データベース内のテーブルをリストボックスで選択できるようにすれば、VBAに不慣れなメンバーでも簡単に利用できます。
品質レポートの自動生成: NULL/空データの「割合」を算出し、閾値（例：95%以上データが存在すること）を設定。閾値を下回る列をハイライトしたレポートを自動生成する機能を追加すれば、より実践的なツールになります。
異常値の検出: 件数だけでなく、数値データの最大値・最小値・平均値や、テキストデータの最大長・最小長なども同時に集計するロジックを追加することで、外れ値や異常フォーマットのデータを早期に発見する手がかりを得られます。

まとめ

今回ご紹介したVBAスクリプトは、日々の開発業務で発生する「データ確認」という地道な作業を自動化し、データ品質の初期評価を迅速に行うための実践的なソリューションです。

このようなスクリプトを活用することで、我々開発者は単純作業から解放され、より本質的な課題解決や創造的な作業に集中することができます。ぜひ、このコードをベースにご自身のプロジェクトに合わせてカスタマイズし、データ管理業務の効率化に役立ててください。

頑張らないために頑張る

ゆるく頑張ります