Access で多対多の関係に対応する - TechRepublic

リレーショナルデータはジグソーパズルのようなものだと想像できるかもしれません。
ピースにはほとんど意味がなく、完成品への手がかりもほとんどありません。完成品は
、パズルを一つ一つ丹念に組み立てて初めて明らかになります。リレーショナルデータは
まさにそのパズルのようですが、一つだけ違いがあります。Accessはどのレコードが
他のレコードと一致するかを正確に把握しており、ピースを組み合わせるのはほぼ瞬時に行われます。リレーションシップによって
、これらすべてが背後で実現されているのです。

ほとんどの場合、デフォルトの1対多のリレーションシップで
問題なく動作します。しかし、1対多では不十分な場合は、1対1の
リレーションシップか多対多のリレーションシップかを判断する必要があります。1
対1のリレーションシップは最も一般的ではなく、
外部キーフィールドに一意のインデックスを設定するだけで簡単に実現できます。多対多のリレーションシップは、
この記事で説明するように、より多くの作業が必要です。

3つのタイプの関係

リレーションシップとは、2つのテーブル間の関連付けであり、
Access が複数のテーブルに格納されている関連データをまとめて取得できるようにします。
リレーションシップには多くの種類があり、Access は次の3種類をサポートしています。

• 1 対多は最も一般的な
  関係であり、主キーの値が
  関連テーブル内のどのレコードとも一致しないか、1 つ、または複数のレコードと一致します。
• 1対1はおそらく3つの中で最も
  一般的ではありません。主キーの値が1つの
  レコード（または全くないレコード）にのみ一致する関係です。これらの関係はほとんどの場合、ビジネスルールによって強制され、
  実際のデータから自然に導き出されることは稀です。
• 多対多の関係
  は、両方のテーブルに複数のレコードに関連付けられたレコードが含まれている場合に発生します。Access
  は多対多のリレーションシップを直接サポートしていないため、
  「関連テーブル」と呼ばれる3つ目のテーブルを作成する必要があります。このテーブルに
  は、各データテーブルへの主キーと外部キーが含まれます。多対多のリレーションシップを分解すると、関連テーブルと2つのデータテーブルの間に
  2つの1対多のリレーションシップが作成されます。
  

多対多を見つける

多対多関係を見抜く鍵は、
データを理解することです。すべてのデータがどのように組み合わさって
必要な結果を生み出すのかを深く理解することほど、何にも代えがたい価値があります。
データに精通していればいるほど、設計プロセスで多対多関係を見抜くのが容易になります
。見落としていた関係は、
開発プロセスでプロトタイプフォームを作成する際に明らかになるはずです。例えば、
書籍を追跡するためのシンプルなデータベースの初期設計プロセスの後には、表A
に示すようなテーブル仕様が既にできているかもしれません。

主キー インデックスの他に、各テーブルには一意のインデックスがあります。

• 書籍:
  タイトルISBN
• 著者:
  姓 名

図 A は、
書籍と 著者間の 1 対多の関係を示しています。

図A

Access は、2 つのテーブル間に既定の 1 対多の関係を割り当てます。

図Bは、
典型的なデータ入力フォームを表しています。（オートフォームウィザードを使用し、
Authorsテーブルに基づいてフォームを作成しました。）ウィザードは、
メインフォームとサブフォームの配置で一対多のリレーションシップを自動的に表現します。メインフォームには リレーションシップの「一」側（Authorsテーブル）
のレコードが表示され、サブフォームには「多」側（Booksテーブル）のレコードが表示されます。このフォームは、 同じ書籍の複数の著者を処理できるように見えますが、 次の書籍レコードを入力してみてください。

• 
  Microsoft Office System 2003アップグレードガイド、0-7897-3176-2、Mike Gunderloy および
  Susan Harkins著
• 
  VBA による Microsoft Access 2003 の自動化、0-7897-3244-0、Mike Gunderloy および Susan
  Harkins著

図Bは
、Mike Gunderloyの帳簿情報を入力した後のSusan Harkinsのレコードを示しています。
レコードを保存しようとすると、
サブフォーム内の帳簿エントリがテーブルの一意のインデックスに違反しているため、Accessはエラーを表示します（これらの帳簿情報は、
Mike Gunderloyのレコードを入力したときに入力しました）。

図
Cに示すように、Accessは著者名と書籍レコードの両方を保存しましたが、これらの書籍はMike Gunderloy（AuthorIDFK値1）にのみ関連しています。 これらの2冊の書籍を複数の著者に
関連付ける方法はありません。タイトルフィールド とISBNフィールドに一意のインデックスが設定されていないため、Accessは各書籍レコードを2回保存しますが、データの重複は 正規化の第1正規形に違反します。つまり、 各書籍には1つのレコードしか存在しないはずです。

図B

このフォームは複数の著者による書籍を処理できると思われるかもしれませんが...。

図C

…しかし、Access では書籍を 2 番目の著者に関連付けることはできません。

関係を活かす

多対多の関係に対応するのは、次の 4 つの簡単な
手順です。

1. 
   2 つのテーブル間の既存の関係を削除します。
2. 
   関連テーブルを作成し、関連する両方の
   データ テーブルに外部キー フィールドを含めます。
3. 各データ テーブルと関連テーブル内の適切な外部キー
   の間に 1 対多の関係を作成します。
   
4. 元の 1 対多の関係の多
   テーブル内の外部キー フィールドを削除します。
   

これらの手順を書籍追跡の例に適用するには、まず
「リレーションシップ」ウィンドウを開き、「Books」と「Authors」の関係を削除します
（2つのテーブル間の結合線を右クリックし、「削除」を選択します）。
次に、「AuthorsBooksmm」という名前の新しいテーブルを作成し、次の3つのフィールドを設定します。

• 著者BookID
  （自動採番）
• BookIDFK
  （番号）
• 著者IDFK
  （番号）

(テーブル名に接尾辞mm
を追加すると 、テーブルが関連テーブルであることを識別しやすくなります。mmは多対多を表します。)

次に、BookIDFKフィールド
とAuthorIDFKフィールドの両方に一意のマルチフィールドインデックスを割り当てます。リレーションシップウィンドウを開き、AuthorsBooksmmを追加して、図D
に示すように、以下の1対多リレーションシップを作成します。

• Books:BookID
  から AuthorsBooksmm:BookIDFK
• Authors:AuthorID を AuthorsBooksmm:AuthorIDFK に変更します。

リレーションシップウィンドウを閉じて変更を保存します。次に、
Booksテーブルを開き、AuthorIDFKを削除します。Authorsテーブルを閉じて保存します。

図D

2 つのデータ テーブルと関連テーブルの間に 1 対多の関係を作成します 。

一般的なルールとして、関連テーブルを介して多対多の
関係に対応する場合は、クエリに基づいてフォームを作成する必要があります。

• 複数のテーブルと1 つの
  テーブルの両方から、変更する必要がある、または必要になる可能性のあるすべての非キー フィールドを追加します。
• 関連テーブルから一
  側を表す外部キーを追加します。
  

例を続けるには、図Eに示すクエリを作成し、AuthorsBooksという名前で保存します。現時点では、 2冊の書籍をどちらの著者にも関連付ける
レコードが関連テーブルに存在しないため、クエリにはレコードが表示されません。AuthorsBooksmmを開き、図F に示す2つのレコードを入力します。 これらのレコードは、両方の書籍レコードを著者Mike Gunderloyに関連付けます。

図E

このクエリに基づいて新しいデータ入力フォームを作成します。

図F

これら 2 つのレコードにより、両方の本が Mike Gunderloy にリンクされます。

フォームウィザード（オートフォームウィザードではありません）を使用して、「AuthorsBooks」クエリに基づいて新しいフォームを作成します。図G に示すように
、すべてのフィールドを追加し、サブフォームビューを選択します。 完成したフォームでは、両方の書籍レコードの著者としてMike Gunderloyが正しく表示されます 。

図G

フォーム ウィザードは、3 つのテーブル間の関係を認識します。

この時点では、両方の書籍レコードに「Susan Harkins」を簡単に追加できますが、特に 既にデータが存在するリレーションシップを変換する場合は
、状況が少し複雑になります。「Susan Harkins」 は既にAuthorsテーブルに存在するため、 サブフォームに「Susan Harkins」と入力すると重複エラーが発生します。一意のインデックスがない場合、Accessは 「Susan Harkins」と入力して重複レコードを作成してしまう可能性があります。

簡単な解決策としては、サブフォームの外部キーフィールドを
コンボボックスに変換し、リストから既存の著者を選択するか、
新しい著者を入力します。この操作を行うには、完成したフォームをデザインビューで開き、外部キーフィールドの連結コントロール（AuthorIDFK）をコンボボックスに変更します（ コントロール
を右クリックし、「変更」を選択して「コンボ ボックス」を選択します）。コンボボックスコントロールの「値集合ソース」プロパティを次のSQL ステートメントに設定します。

AuthorID、LastName、FirstName を Authors から選択し、LastName で ORDER BY する

さらに、「列数」プロパティを3に設定します。
フォームビューに戻り、コントロールのドロップダウンリストを表示します（図Hを参照） 。Susan Harkinsを選択し、
2番目の書籍レコードに対しても同じ操作を繰り返します。

図H

新しいコンボ ボックス コントロールのドロップダウン リストから作成者を選択します。

3つのテーブルすべてを開くと、それぞれの著者と書籍が1つだけ出現していることがわかります
。関連テーブルは両方の著者を両方の書籍に関連付けており、これは図I
に示すAuthorsBooksクエリを実行すると簡単に確認できます。

図I

関連テーブルはこれらの関係を維持します。

親族から隠れないで

多対多の関係に戸惑う必要はありません。
この記事で紹介した簡単なガイドラインに従って、関連テーブルを追加し、それらの関係を適切に維持してください。そして、 正しく設定が完了したら、
その関係を自由に活用してください。

スーザン・セールス・ハーキンスは
独立コンサルタントであり、データベースとWebテクノロジーに関する記事や書籍を多数執筆しています
。最近の著書には、 『Automating
Microsoft Access 2003 with VBA』、『Upgrader’s
Guide to Microsoft Office System 2003』、『ICDL
Practice Questions Exam Cram』、『ICDL
Exam Cram 2』、マイク・ガンダーロイとの共著『 Absolute Beginner’s
Guide to Microsoft Access 2003』、そして『Absolute
Beginner's Guide to Microsoft Access 2002』が
あり、いずれもQue社から出版されています。現在、スーザンはDatabase Advisorsの出版担当ディレクターとしてボランティア活動を行っています
。