関連性で何が出来るのか(ExcelでRDBその9)

ExcelRDB(リレーショナルデータベース)を操作してみようという連載のその9です。前回の記事はこちらです。
hirocom777.hatenadiary.org

関連性をどう使うか

 前回は、データベースのテーブルに外部キーを設定して2つのテーブルに関連性をもたせました。でも、結局これを設定すると何ができるんでしょうか?今回はそこを探っていこうと思います。

複数のテーブルからデータを取り出す

 関連性を持たせた複数のテーブルを組み合わせてデータを抽出することが出来ます。今回は、以下のファイルを用意しました。
09_売上.accdb - Google ドライブ
このファイルには"売上明細"、"商品データ"の2つのテーブルで構成されています。それぞれ以下の様にデータが入っています。

・売上明細

f:id:HiroCom777:20201123230439j:plain
・商品データ
f:id:HiroCom777:20201123230543j:plain

2つのテーブルは"商品ID"で関連性が持たれています。このファイルを置いてある場所にADOを参照設定してあるExcelファイルを作って、空のシートモジュールに以下のコードを入力、実行してみてください。

Sub JoinDB()

Dim fileName As String
fileName = "09_売上.accdb"

Dim adoCn As Object
Set adoCn = CreateObject("ADODB.Connection")
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & fileName & ";"

Dim adoRs As Object
Set adoRs = CreateObject("ADODB.Recordset")

Dim sqlCmd As String
sqlCmd = "SELECT 売上明細.ID,売上明細.日付,商品データ.商品名,商品データ.単価,売上明細.数量 " _
        & "FROM 売上明細 INNER JOIN 商品データ ON 売上明細.商品ID = 商品データ.商品ID "

adoRs.Open sqlCmd, adoCn 'SQLを実行して対象をRecordSetへ
Me.UsedRange.ClearContents
Me.Range("A1").CopyFromRecordset adoRs 'RecordSetをシートに貼り付け

adoRs.Close
adoCn.Close

Set adoRs = Nothing
Set adoCn = Nothing

End Sub

空のシートには、以下の様に表示されたと思います。

f:id:HiroCom777:20201123232613j:plain
これは、"商品ID"をキーとして"売上明細"、"商品データ"2つのテーブルから必要なデータを合成して抽出した結果です。フィールド名が表示されていませんが、左から"売上明細"のIDと日付、そして"商品データ"の商品名と単価を商品IDをキーとして読み込んでいます。最後は"売上明細"の単価です。

この様に関連性を持たせることによって複数のテーブルから必要なデータを抽出することが出来ます。売上明細のテーブルだけでは具体的に何が売れたのかわかりませんし、金額も不明です。単価と数量が取得できていますのでかけ合わせれば売上金額もわかります。これはSQLのJOINを使用して実現しています。コード内の変数sqlCmd に設定してある文字列でデータベースからデータを抽出しています。他にも色々な記述方法があるのですが、詳細は次回に説明したいと思います。

外部キーは?

 ところで、前回データベースファイル内の2つのテーブル間で外部キーの設定をしたことを覚えていますでしょうか?実を言うと上記で説明したSQLのJOINは外部キーの設定をしなくても使用することが出来るのです。それでは外部キーは、いったい何の役に立つのでしょうか?

 データベースに外部キーを設定すると、該当する箇所に制約をかける事ができるのです。今回の場合、参照する側のテーブル(売上明細)を"子テーブル"、参照される側のテーブル(商品データ)を"親テーブル"と呼びます。外部キーが設定されると、まず子テーブルの該当カラムには親テーブルの参照先カラムに入力されている値以外は入力できなくなります。(入力しようとするとエラーが発生します)また、親テーブルのレコードの参照先カラムの値が子テーブルから参照されている値の場合、該当レコードを削除したりすることはできません。これを外部キー制約と言います。

 つまり、関連性の定義を明確にして管理を厳格にできるってことです。データベースで大切なことですね。

本格的になってきた

 いや、何だか本格的になってきましたね。SQLを使うと色々なことが出来そうです。次回はもうちょっとSQLを追及してみたいと思います。お楽しみに!!
hirocom777.hatenadiary.org

ExcelVBAでAccessファイルを操作する連載はコチラから