07章01節…テーマの把握・伝票をAccessで管理する(概要・理論)
あなたは宅配弁当の発注伝票を管理する予定です。紙伝票のサンプルは以下のとおりです。8枚あるので目を通してください(操作の必要はありません)。
今回は「紙伝票をAccessで管理できるようにするためには、どのようなテーブル構築・解析が必要になるか」、というテーマについて学習します。
まず1枚目の伝票に注目してください。Accessのテーブル構築をする際は演算部分を除外して考えました。よって第一段階では以下のように処理することができます。
次は[お客様番号]と[お客様名]の関係を考えます。[お客様名]は[お客様番号]に従属しているので、マスターを作成すれば[お客様名]を省略することができます。
お客様番号の意味を表す一覧、「T顧客マスター」を作ることになります。
そして伝票から[お客様名]を省きます。
同様に[単価]も[商品名]に従属しているのでマスターを作成すれば[単価]を省略することができます。「T商品マスター」を作成し、伝票から[単価]を省くことになります。
さらに[商品名]はテキスト型なので、数値型に置き換えれば、より適切な状態となります。各商品に通し番号を振るのです。これが完成形の商品マスターです。またコード化したので伝票の[商品名]は[商品番号][商品CD]などに置き換えます(ここでは[商品番号]を採用)。
ここからが新しい考え方の学習です。まずはこの伝票の主キーを発見します。今後何枚も伝票が増えることが想定されます。伝票内の各項目の中で、どの伝票かを特定できる項目・値は[伝票番号]です。すなわちまず、この伝票の中では[伝票番号]が主キーになることを念頭においておきます。
次にこの伝票をリスト化します。すなわち無理にでも伝票内の各値を1行に収めるのです。すると以下のようになるでしょう(先頭行はフィールド名)。
しかしこのようなテーブル構造は不適切です。テーブル構造を1度決めてから、あとで修正するのは非常に大変です。ところが上のテーブルではひとつの伝票で管理できる商品数が3つまでと決まってしまいます。
もしひとつの伝票で5種類の商品を処理しなければならなくなった場合にはテーブルの構成を変更する必要が出てしまいます。例えば伝票番号「1002」のレコードを追加する際に、以下のようにテーブルデザインの変更をする必要が発生します。
これでは万一全商品を注文する顧客が出た場合には、膨大な列数のテーブルに変更しなければならなくなります。また、商品数が増えたときにも変更が大変です。このように、ひとつの伝票の中に出現回数が決まっていない項目がある場合には、必ず実行する作業があります。
【絶対に一度だけ出現する項目と、出現回数が決まっていない項目を切り離す】作業です。具体的に[伝票番号]が「1001」のレコードで考えると以下のようになります。
そのあと、それぞれを別にテーブル化します。以下は[伝票番号]が「1002」までのレコードを入力した結果サンプルです。なお、伝票の中で一度しか出現しない項目を集めた側のテーブル名を「T伝票A主部」、複数回出現する可能性がある側のテーブル名を「T伝票B明細部」として考えましょう。
「T伝票A主部」に関してはこれで完成です(あとはデータ入力)。「T伝票B明細部」に関してはまだ修正する個所があるので検討します。このままでは「いつ」「誰に」販売したのかを説明することができません。そこで明細側のテーブルに、主部の主キー(ここでは「伝票番号」)を加えます。実際には以下のように修正します。
これでほとんど修正は完了していますが、「T伝票B明細部」には主キーとなりうるフィールドがありません。そこで主キー用のフィールドとして適当なID番号をオートナンバー型で設定するのです。今回は[明細ID]という名前でオートナンバー型のフィールドを追加します。このフィールドに特に意味はありません。しいていうならば入力された順番を管理しているくらいでしょうか。これで完成です。
このようにひとつの伝票をAccessで管理するためには、無駄のない適切なテーブルを構築する必要があります。このテーブルを構築するための分割作業を【正規化】とよびます。元の伝票をAccessで管理するにはテーブルが4つ必要になることを確認してください。もちろん伝票の複雑さが増せばテーブル数も増加します。
あとで集計する場合には各テーブルをリレーションシップで結ぶことになります。
テーブル構築のポイント【まとめ】
※①～③の順は問わない。④は最後を推奨。ノートを使ってイメージを作成しておく。
- 何度も重複する値が利用されるテキスト型のフィールドはマスターを作り数値型にする【コード化①】
- 演算部はテーブル構築から除外して考える(リスト内に演算部があればテーブル構築後クエリを作っておく)【演算部の除外②】
- 従属性がある場合には関連性をマスターテーブル側に記述する。メインリストからは除外する【従属性の考慮③】
- 主部と明細部がある伝票の場合はそれぞれを分ける。明細部には主部の主キーとなるフィールドと連番フィールドを付ける。【主部と明細部の分割④】