【エクセルVBAでAccess連携】SQLのJOINを使って複数のテーブルを組み合わせてデータを取り出す

photo credit: Merger via photopin (license)

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

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

前回は記事はコチラ。

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

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

Accessデータベースのテーブルから色々なパターンでデータを取り出す方法をお伝えしました。

さて、データベース内に複数のテーブルがあるときに、データを取り出しつつ組み合わせたいということがありますよね。

それぞれのテーブルからSELECT文で抽出して、後でVLOOKUP関数…でもできるのですが、SQLを使えばたったの一文で複数のテーブルを組み合わせてデータを抽出することができます。

ということで、今回はエクセルVBAでAccessデータベースの複数のテーブルを組み合わせてデータを取り出す方法についてお伝えします。

では、行ってみましょう！

お題として使うデータベースについて
おさらい：シンプルに１つのテーブルからデータを抽出
JOINで複数のテーブルを組み合わせて抽出
まとめ
連載目次：エクセルVBAでエクセルからAccessデータベースを操作する

お題として使うデータベースについて

まず、今回のお題として使うデータベースについて説明をしておきます。

Accessデータベースファイル「test2.accdb」には二つのテーブルがあります。

一つ目のテーブルは「人口」で、都道府県別の人口に関するデータを持ちます。

もう一つのテーブルは「市区町村」で、都道府県別の市区町村数に関するデータを持ちます。

ちなみにテーブル「市区町村」のほうは、北海道・東北・関東の都道県についてはデータがありません。

おさらい：シンプルに１つのテーブルからデータを抽出

先におさらいとして、１つのテーブル「人口」から、フィールド「ID」「都道府県」「人口」を抽出してみます。

プログラムはこちらです。

Sub JoinDB()

Dim strFileName As String
strFileName = "test2.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 ID,都道府県,推計人口 FROM 人口"

adoRs.Open strSQL, adoCn 'SQLを実行して対象をRecordSetへ

With Worksheets(1)

    .UsedRange.ClearContents
    .Range("A1").CopyFromRecordset adoRs 'RecordSetをシートに貼り付け

End With

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

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

End Sub

実際にデータを抽出する命令であるSQL文は14行目に記述されています。

テーブルからデータを抽出する場合はSELECT文を使い

SELECT フィールド名1,フィールド名2,… FROM テーブル名

とします。

他のプログラムは一切いじらずに、このSQL文を変更するだけで複数のテーブルを組み合わせてデータを抽出できるようになります。

ところで、ADODB.Connectionオブジェクト、ADODB.Recordsetオブジェクトとは何か、など他の部分については以前の記事を参考にしてくださいね。

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

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

JOINで複数のテーブルを組み合わせて抽出

ではエクセルVBAでこれらの二つのテーブルを組み合わせてデータを抽出してみましょう。

SQL文にJOINを使っていきます。

JOINにはいくつか種類があるのですが、今回は主要なINNER JOINとLEFT JOINの二つについて説明をしていきます。

LEFT JOINの使い方

LEFT JOINを使って、二つのテーブルを組み合わせてデータを抽出するSQL文は

SELECT テーブル名.フィールド名,… FROM 左テーブル名 LEFT JOIN 右テーブル名 ON 左テーブル名.フィールド名=右テーブル名.フィールド名

と書きます。

まずSELECTのすぐ後の「テーブル名.フィールド名,…」ですが、これまでのSELECTと基本は同じで、抽出するフィールドをカンマ区切りで指定します。

なぜピリオドでテーブル名を指定しているかというと、今回は取り扱うテーブルは複数あるので、どちらかを指定しないとダメだからです。

FROMの後には一つ目のテーブル（左テーブルとも言います）を指定、LEFT JOINの後に二つ目のテーブル（右テーブルとも言います）を指定します。

ONの後には結合条件を書きます。結合条件というのは、指定した２つのフィールドの値が一致していればそれを同じレコードとして結合する、ということを表しています。

LEFT JOINの例

SQL文を以下に設定してみましょう。

strSQL = _
    "SELECT 人口.ID,人口.都道府県,人口.推計人口,市区町村.区の数 " & _
    "FROM 人口 " & _
    "LEFT JOIN 市区町村 " & _
    "ON 人口.ID=市区町村.ID"

このSQL文の抽出条件としては

「人口」テーブルからフィールド「ID」「都道府県」「推計人口」を抽出
「市区町村」テーブルからフィールド「区の数」を抽出
フィールド「ID」を結合条件に

となります。実行結果はこちらです。

セルD1からD14までが空欄になっていますが、これは右テーブルつまり「市区町村」には該当のIDのレコードが存在していないので空欄となっています。

セルD15以降は、該当IDがありますので、二つのテーブルのレコードを結合することができています。

INNER JOINの使い方

INNER JOINの使い方ですが、こちらです。

SELECT テーブル名.フィールド名,… FROM 左テーブル名 INNER JOIN 右テーブル名 ON 左テーブル名.フィールド名 = 右テーブル名.フィールド名

書き方や要素はLEFT JOINと全く一緒です。

INNER JOINの例

SQL文を以下に設定します。

strSQL = _
    "SELECT 人口.ID,人口.都道府県,人口.推計人口,市区町村.区の数 " & _
    "FROM 人口 " & _
    "INNER JOIN 市区町村 " & _
    "ON 人口.ID=市区町村.ID"

前述のSQL文の「LEFT」を「INNER」に変更しただけですね。

実行してみますと

ID1～14までの北海道、東北、関東の都道県が表示されていませんね。

LEFT JOINとINNER JOINの違い

LEFT JOINは左テーブルに存在するレコードに関しては、右テーブルに結合条件にマッチするレコードがなかったとしても全て抽出されます。

このような結合を外部結合と言います。外部結合にはLEFT JOINのほかに、右テーブルに存在するレコードを全て抽出するRIGHT JOINがあります。

一方でINNER JOINは右テーブル、左テーブルともに結合条件に一致したレコードのみを抽出します。このような結合を内部結合と言います。

目的に応じて使い分けて頂ければと思います。

まとめ

エクセルVBAでAccessデータベースの複数のテーブルを組み合わせてデータを取り出す方法についてお伝えしました。

SQL文にJOINを使いますが、外部結合LEFT JOINと内部結合INNER JOINと２種類がありますので、目的に応じて使い分けをして頂ければと思います。

これまで何回かに渡ってテーブルからデータを抽出する方法についてお伝えしてきましたが、プログラムはいじらずにSQL文を変えるだけで色々な抽出ができるということを覚えておいて下さい。

次回はテーブルにレコードを追加する方法についてお伝えしたいと思います。

【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法

エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はデータベースにレコードを追加するAddNewメソッドを使ったシンプルな方法についてお伝えします。

どうぞお楽しみに！

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

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