エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方

photo credit: thomas@flickr Keep Writing Day & Night via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。

前回の記事はコチラ。

エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方

エクセルVBAでAccessデータベースを操作する方法についてのシリーズです。今回はデータベースのレコードをグループで集計して抽出する方法です。SQL文のGROUP BY句と集計関数を使いますよ。

Accessデータベースのレコードをグループで集計して抽出する方法として、SQL文のGROUP BY句の使い方をお伝えしました。

今回ですが、そのGROUP BY句と合わせて使われることの多いHAVING句について紹介します。

主にGROUP BYで集計して抽出したレコードを条件づけて絞り込む際に使うのですが、集合関数と組み合わせてテーブルの状態を判定する方法についてもお伝えします。

では、よろしくお願いいたします！

お題：エリア単位で集計しつつ条件を満たすエリアのみ抽出する
データベースからレコードを抽出するプログラム
HAVING句で集計関数を使った条件付けによるレコード抽出
1. HAVING句でグループ化した結果を絞り込む
2. HAVING句を使ってテーブルの状態を判定する
まとめ
連載目次：エクセルVBAでエクセルからAccessデータベースを操作する

お題：エリア単位で集計しつつ条件を満たすエリアのみ抽出する

まずは今回のお題の確認からです。

Accessデータベース「test.accdb」に、テーブル「データ」があります。

フィールドの構成は

｜ID｜エリア｜都道府県｜都道府県庁｜推計人口｜面積｜人口密度｜国勢調査人口｜市の数｜区の数｜町の数｜村の数｜

です。

テーブルのレコードは都道府県単位ですが、前回の記事でSQL文のGROUP BY句を使って、エリア単位で抽出する方法についてお伝えしました。SUMやCOUNTなどの集合関数と組み合わせることで、エリアごとの推計人口と面積の合計や、エリアごとの都道府県数なども自在に抽出することができます。

さて、今回ですがその抽出したエリアのうち

面積が一定以上のエリアのデータのみ抽出する
都道府県数が一定数以上のエリアのデータのみ抽出する

などといった、グループ集計結果の絞り込みをしていきたいと思います。

データベースからレコードを抽出するプログラム

続いて、ベースとなるプログラムを紹介しておきます。

前回と同じものです。

Sub SelectDB()

Dim strFileName As String
strFileName = "test.accdb"

Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続

Dim adoRs As Object 'ADOレコードセットオブジェクト
Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成

Dim strSQL As String
strSQL = "SELECT * FROM データ"

adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ
Worksheets(1).Range("A1").CopyFromRecordset adoRs

adoRs.Close 'レコードセットのクローズ
adoCn.Close 'コネクションのクローズ

Set adoRs = Nothing
Set adoCn = Nothing  'オブジェクトの破棄

End Sub

Accessデータベース「test.accdb」とコネクションを開いて、SQL文を使ってデータを抽出するものです。14行目でSQL文を指定しますが、現在は全てのレコードを抽出する形になっていますね。このSQL文を抽出したい内容に指定していけばよいということになります。

このプログラムについては以下の記事もご覧ください。

【エクセルVBA＆Access連携】SQL文でデータを抽出する最も簡単なプログラム

エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はデータベース言語SQLとADODB.Recordsetオブジェクトでデータベースからデータを抽出します。

HAVING句で集計関数を使った条件付けによるレコード抽出

条件を満たすレコードを抽出というと、WHERE句を思い出すかも知れません。

SQL文はこんな書き方でしたね。

SELECT フィールド名 FROM テーブル名 WHERE 条件文

【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法

エクセルVBAでAccessデータベースを操作するシリーズ。今回はAccessデータベースのテーブルから色々なパターンでデータを抽出するSELECT、WHEREの使い方についてお伝えします。

例えば、GROUP BYでエリア単位にグループ化した結果に対して、面積が300000以上のエリアのみに絞り込みをしたい場合

strSQL = "SELECT エリア,SUM(面積) FROM データ GROUP BY エリア WHERE SUM(面積)>=30000"

こんなふうに書きたくなります。ですが、これを実行すると

このように「WHERE句（SUM(面積)>=30000）で集計関数を使用することはできません。」という実行時エラーが発生してしまいます。

わかりやすいですね。WHERE句では集計関数を使えません。

集計関数を使って条件付けをしたい場合はHAVING句を使うのです。

HAVING句でグループ化した結果を絞り込む

GROUP BY句でグループ化したレコードに対するHAVING句の使い方ですが

SELECT フィールド名1,フィールド名2,… FROM テーブル名 GROUP BY フィールド名A,フィールド名B,… HAVING 条件文

となります。条件文は、集合関数と比較演算子を使うことになりますね。

先ほどのお題である、GROUP BYでエリア単位にグループ化した結果に対して、面積が300000以上のエリアのみに絞り込んで、そのエリアと合計面積を抽出したい場合ですが、

strSQL = "SELECT エリア,SUM(面積) FROM データ GROUP BY エリア HAVING SUM(面積)>=30000"

とします。結果としては

となります。

別の例として、GROUP BYでエリア単位にグループ化した結果に対して、都道府県数が5以上のエリアのみに絞り込んで、そのエリアと都道府県数を抽出したいのであれば

strSQL = "SELECT エリア,COUNT(都道府県) FROM データ GROUP BY エリア HAVING COUNT(都道府県)>=5"

とすればよいですね。

HAVING句を使ってテーブルの状態を判定する

HAVING句は、何もGROUP BY句とセットで使わないといけないというわけではありません。

集計関数を使って条件付けをしたい場合に使うことができるので、例えばこんな使い方もできるのです。

strSQL = "SELECT ""都道府県の数は47ある"" FROM データ HAVING COUNT(都道府県)=47"

どんなSQL文だかわかりますか？

テーブル内のフィールド「都道府県」の数をカウントしてそれが47であれば、「都道府県の数は47ある」という一つのフィールドを持つ一つのレコードが表示せよ、ということです。

もしテーブル内のフィールド「都道府県」の数が47でなければ、抽出レコードがない、つまり何も出力されません。

別の例として、このような使い方もあります。

strSQL = "SELECT ""日本の推計人口は1億2500万人以上"" FROM データ HAVING SUM(推計人口)>=125000000"

テーブル内のフィールド「推計人口」の合計を判定して、1億2500万人以上であれば「日本の推計人口は1億2500万人以上」というフィールドを持つレコードが出力されます。

このように、HAVING句と集計関数を使ってテーブルの状態を判断することができます。

以下記事にあるように、抽出したレコードが空かどうかを、ADODBレコードセットオブジェクトのEOF、BOFプロパティを使って判定することで、テーブルの状態をみて分岐をするようなプログラムを作成することができます。

【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する

エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はエクセルVBAでAccessデータベースに特定のデータが存在するかどうかを判定するプログラムを作ります。

まとめ

エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方についてお伝えしました。

GROUP BY句とセットで使うことが多いですが、それ以外にも集合関数を使ってテーブルの状態をスパっと判断したいというときにも使うことができます。

ぜひ、ご活用ください。

次回はSQL文内にFormat関数を使って日付を条件に色々な抽出を便利にする往訪をお送りできればと思います。

エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出

エクセルVBAでAccessデータベースを操作する方法についてお伝えしています。SQL文にFormat関数を使って特定の日付のレコードを抽出する方法。また応用で特定の年月や曜日でグルーピングもできます。

どうぞお楽しみに！

連載目次：エクセルVBAでエクセルからAccessデータベースを操作する

エクセルと相性の良いデータベースとして第一候補として挙がるAccess。エクセルをインターフェース、AccessをデータベースとしてエクセルVBAを使って連携する方法とその様々なテクニックについてお伝えしていきます。