Accessでリレーションシップとコンボボックスを作る
AccessがExcelとの大きな違いの１つが「リレーションシップ」。Excelの場合には、シート間になんら因果関係もなくデータを作れますが、それが故にシートAとシートBとの間に同じようなデータがある場合、「二度手間」「情報の乖離」「ゴミデータ」が生まれます。
また、Excelでもデータの入力規則にてセル範囲を利用した「ドロップダウンリスト」を構築できますが、「本当はコードを選ばせたいけれど、コードの名前を設定せざるを得ない」といった事がしばしば。Accessの場合にはコード名を選んでもデータとしては、コードをセットする事が可能です。
今回はこの部分をまとめます。
目次
今回使用するファイル
リレーションシップについて
リレーションシップとは、予めテーブルとテーブルの間に関連性をもたせて、データの登録や削除に伴う問題を手動では行わず、自動的に「連鎖更新」や「連鎖削除」などを実現する為のものです。そのため、例えば「マスターには対象のIDがいない」時に、「サブマスターにそのIDを登録しよう」とすると、弾かれます。
クエリ作成時のフィールド間の連結(LEFT JOINなど）と似ていますが、目的が異なります（クエリの時はその時だけのVLOOKUPで結合するのと同義。リレーションシップは恒常的に結合状態となる）。
連鎖更新
商品マスタテーブルと、その受注を管理してる受注テーブル。２つのテーブルがあった時、「商品マスタのIDが変更になった」といった場合、マスタ側はその対象の商品のIDを書き換えるだけで済みますが、大量の受注テーブルにあるIDはそのままでは、手作業で変更を掛ける必要があります。
この時、連鎖更新を設定している場合、マスターテーブルである商品マスタテーブルのIDを書き換えると、連結先の受注テーブル側の商品のIDも自動ですべて書き換えてくれる。これが、連鎖更新です。手作業で書き換えなど無用となります。
作り方
Accessのメニューから作業を行います。今回はシンプルに「商品マスタ」「受注テーブル」を用意。商品マスタ側は「商品ID、商品名」、受注テーブル側は「受注ID、商品ID」とし、両方の商品IDを連結して連鎖更新をセットします。
- メニューより「データベースツール」をクリック
- リレーションシップをクリックする
- なにもないエリアで右クリック⇒テーブルの表示をクリック
- 追加するテーブルを選んで、追加ボタンで2つのテーブルを追加する
- 商品マスタ側の商品IDを掴んだまま、受注テーブル側の商品IDまでドラッグして離す
- 参照整合性のチェックを入れる
- フィールドの連鎖更新にチェックを入れる
- 作成ボタンをクリックする
- デフォルトだと「1対多」の結合で作られます。
- 変更したい場合は、結合されてる線をダブルクリックすると変更が可能です。
- 結合の種類をクリックすると、クエリ作成時のそれと同じように、結合の種類を選べます。変更すると、9.の設定が変わります。通常は変更する必要はありません。
- 閉じるボタンクリックして閉じれば、リレーションシップが保存されます。
図：連鎖更新のリレーションシップの作り方
実際に更新されるか試してみる
この状態で、両方のテーブルにデータを入れておきます。この状態で、商品マスタ側のとある商品のIDを変更してみます。受注テーブルには商品IDが1のデータが、3個あります。商品マスタ側のIDが1のデータのIDを、11に変更してみましょう。
すると、受注テーブル側の商品IDが1のデータは全て、11になりました。これが連鎖更新です。IDだけでなく例えば商品ジャンルなどのジャンル名通しを結合して、ジャンル名を書き換える必要がある時、書き換えると、受注テーブルが書き換わるので、手動でやる時の変更漏れなどが一切なくなります。
医薬品のようなGS1コードが途中で変更されるようなケースなどでは非常に有効ですね。変更前コードと変更後コードが一発で書き換わるので、データの連続性を保つ事が可能です（但し、書き換わってほしくないケースでは連鎖更新は外しておくと良いでしょう）
図：商品IDが1のアイテムのIDが全部11に自動で書き換わった！！
注意点
- 通常マスターデータのIDは、オートナンバー型で設定する事が多いですが、今回のような商品マスタの場合、その商品固有のコードを付ける事がほとんどなので、商品IDのデータ型は「数値型」にしておきましょう。文字を含む場合には、連番のID列（オートナンバー型）を設けて、別に商品ID列(短いテキスト型）を設置するのが定石です。
- リレーションシップを貼る場合には、マスタ側の対象のフィールドは通常「主キー」となってるものを選びます。
- 商品IDのような数値やコードを連鎖更新の対象に選ぶ理由は、通常の商品名のような場合、同じ名前のものを誤って登録する可能性があるからです。また、主キー設定してるものを選ぶ理由も同じで、この場合そのフィールドのプロパティにある「インデックス」が重複なしにしてあるので、同じコードの入力を防げます。
- 文字列を使いたい場合には、インデックスに於いて「重複なし」としてあげることで、同じく重複登録を防ぐ事が可能です。
- 受注テーブル側は商品IDが重複登録されていて当たり前なので、このような商品ID列にインデックス設定は不要です。
図：型の設定とインデックスの設定も肝になります。
連鎖削除
連鎖更新の仕組みと同じく用意されているのが、連鎖削除。作り方は連鎖更新と同じで、「連鎖削除」にチェックを入れるだけです。注意点等についても同じ。単純にマスタ側で対象のマスタデータが削除されると、連結してるテーブル側でもそのコードに該当するレコードを削除します。
実際に連鎖削除をセットして、商品マスタ側で一つ商品データを削除してみましょう。今回は商品IDが1のものを削除してみます。受注テーブル側には対象の商品IDを持つレコードが3件あります。削除を実行すると下記のようなメッセージが出て、実行すると、受注テーブル側から対象の3件のレコードが削除されます。
データではなくレコードが削除されますので、ご注意ください。
図：連鎖削除の警告ダイアログが出る
登録の不整合を防ぐ
リレーションシップを貼ることで、例えばこれらの２つのテーブルを結合させたクエリを作成する場合、はじめから結合がセットされた状態となっています。クエリ画面で外すことは可能ですが、リレーションシップ画面では結合はそのままです。そのクエリでだけリレーションシップが外れている状態になります。
また、参照整合性にチェックを入れてると、商品マスタには存在しないIDを受注テーブル側で入力するといった事を阻止する事が可能です。試しに入れてみると以下の画像のようなエラーが出て、入力を拒否されます。拒否されたものは、ESCキーを叩くことでキャンセルされます。
図：参照整合性が働いて、存在しないIDの登録は拒否される
コンボボックスについて
今回のリレーションシップの話題と地味に深い関係にあるのがこのコンボボックス。それそのものは、一見するとExcelでもおなじみの「データの入力規則を使ったドロップダウンリスト」に見えます。実際に使う目的自体は全く同じものです。
しかし、今回のテーブル構成を見た場合、ユーザに商品IDを入力（選択）させるのが適当か？といったら、不適当ですね。商品IDで商品覚えているユーザはいません。商品名が必要です。けれども、受注テーブル側には商品名を入れる場所はありません。なぜこのような仕組みになっているのか？
答えは、同じデータを二度も三度も入れさせない為。商品IDがあればクエリにて商品名は後からでも引っ張れます。また、こうする事でデータ量を減らせ、検索スピードの向上にも繋がります。受注テーブルには商品IDが入っていればそれで事足りる。けれど、ユーザに選択させる上では問題だ。これを解決するのが、Accessのコンボボックスなのです。
作り方
Excelのデータの入力規則の場合、列として指定するのは1列のみです。Accessでもそういった作り方は可能ですが、今回のようなケースの場合、2列以上が必要になります。また、今回は商品名を選択すると、商品IDがテーブルに入るようにしなければなりません。作り方は以下の通りです（受注テーブルのフォームをまず作っておきましょう。今回はデータシートの形式でフォームを用意しました）
- 受注テーブルのフォームを開きます。
- 編集モードに切り替えます。
- 商品IDのテキストボックスを右クリック⇒コントロールの種類を変更⇒コンボボックスにする
- コンボボックスを右クリック⇒プロパティを開く
- 「値集合ソース」があるので、「…」をクリックする
- テーブルとして追加するのは、今回は商品マスタです。追加したら閉じます。
- IDと商品名をダブルクリックして追加し、閉じるボタンをクリック
- ここが重要ですが、プロパティの連結列です。1がデフォルトで入っています。これは1列目を入力対象にするという意味です。ID,商品名の順番ならば、1列目が入力したいものなので、1を入力します。
- 入力チェックプロパティはコンボボックスにある値以外を入れられるようにするかどうか？です。ここは「はい」にしましょう。
- 値集合ソースの値のみ表示プロパティは「はい」にしておきます。
- 続けて、プロパティパネルの上の方にある項目をセットします。
- 列数プロパティはお好みですが、2列指定しているので、2と入れます。
- 列幅プロパティはコンボボックスをクリックした時に表示される各列の幅（cm）です。IDは小さめ、商品名は大きめにしたいので、2cm;5cmとでも入れておきましょう。
- 列に何の値なのかわかりやすくする為、列見出しは「はい」にしておくと良いでしょう。
- リスト行数はコンボボックスをクリックした時に一度に表示する件数です。デフォルトのままで十分ですが、候補が多い場合には増やしても良いです。
- リスト幅プロパティはコンボボックスをクリックした時に出てくる候補の横幅。13で指定したcmの合計値でも入れておくと良いです。自動ですと、足りない場合があります。
- これでフォームを閉じます。
- これでコンボボックスの設定は完了です。
図：この4箇所が重要なポイント
図：この4箇所も重要なポイント
実際に使ってみる
設定したコンボボックスを実際に使ってみましょう。
今回はデータシート形式なので、レコードが雑然と並んでいます。が、商品ID列の一番下のレコードをクリックすると、コンボボックスが登場し、リストが出てきます。今回の設定により「見出し付き」「IDと商品名を表示」「連結するのはID列」となっている為、商品を選ぶと商品IDには、商品IDが格納されます。
前回の設定でID列の表示は不要という事であるならば、前回の項目の13.の列幅は0cm;5cmとでも入れておくと良いでしょう。ID列幅が0cmなので非表示になります。また、入力チェックを「はい」にしてるので、値集合ソースの値以外を入れると警告画面が出るようになっています。
そして、選択すると商品ID列には商品名ではなく、IDの値が入るようになっています。こうする事で入力者の負担を減らすだけでなく、入力ミスを無くしたり、リスト外の値を入れられる心配を防ぐ事が可能になります。リレーションシップも貼っているので、二重で入力制限を加えられているので、より確実なリスト入力を実現できます。
図：商品名とIDを表示したリストを表示している様子
図：リスト外の値を入れると弾かれる
注意点
今回は割と厳格な入力制限を施したコンボボックスを作成いたしました。Excelの高すぎる自由度の弊害を排除し、より正しく確実なデータ入力を行うために、またそのデータの整合性を確保するためにリレーションシップを設定しました。しかし、必ずしもこの方法が良いとは限りません。
性別などの入力は2種類しかないわけですから、わざわざテーブル化してクエリで入れる必要性もないでしょう。また、あまりにもリストの候補が多すぎる場合、コンボボックスで何万行もある中から選ぶのは生産的とは言えません。故に時にはリスト外のデータも許容するか？それとも、入力時に商品マスタに登録する画面を出すか？（今回のプロパティで言えば、リスト項目編集用フォームという項目がソレ）といった更に細かい気配りが必要になります。
また、値集合ソースは作り置きしておいたクエリも指定可能なので、膨大なリストの中で例えばあるジャンルの商品だけ絞って表示させることも可能です（商品マスタにジャンル列を設けて、クエリで条件をつけて抽出したものをコンボボックスで使う）。
