ＡＣＣＥＳＳのテーブル設計とクエリ

質問

ＡＣＣＥＳＳ初心者です。
ＡＣＣＥＳＳ２０１０　ＯＳはWindows７です。

パソコンはアクセスとエクセルマクロの入門書を読み終えたレベルです。
只今、注文実績管理表を作成するために、必要な情報を複数のテーブルに
分解してコード化していますが、関連付けできない為にデーターの参照ができません。
対応方法があれば教えていただくようお願いします。
どうしても実現したいので、テーブルの設計が間違えているかもしれませんが写真を掲載します。

■現在の状況と質問
Ａ）商品だけで300アイテムあります。リストを見ながら商品名と金額を入力するのは、膨大な時間を奪われるので、発注書に書かれている。4ケタの商品番号と注文数量の入力のみに
したい。その後に入力した４ケタの数字でデーターを集計し、分析業務につなげていきたい
Ｂ）【Ｃ】と【Ｄ】は注文書のイメージです。ひとり最大50個の商品を一枚の注文書で
発注します（ひとりの注文が1か月に1～3回）。一回の注文で最大、50品目のデーターを
格納することは、アクセスで可能なのでしょうか？
Ｃ）発注実績の抽出ですが、以下内容で実行したいです。どうすれば可能でしょうか？
　　テーブルの設計と関連付け、リレーションの設定がわかりません。
　　i）個人別の1か月注文実績。商品名、注文合計数、合計金額を商品別に表示
　　ii）個人別の注文合計金額（月、年間）の集計。
　　iii）商品Ａの注文実績（月、年間）
Ｄ)リレーションの状態。
【Ａ】の(2)と【Ｃ】の(2)　
【Ｂ】の(2)と【Ｄ】の(3)、(5)、(7)、(9)、(11)、(13)、(15)、(17)、(19)・・・
【Ｃ】の(1)と【Ｄ】の(2)のみ参照整合性がとれています。
Ｅ）商品テーブルのドリルボタンを押して、下の階層を見ようとすると、画像のような
メッセージがでます。これはなんなのでしょうか？

■現在の状況を列挙します。
【Ａ】顧客テーブル
(1)顧客ID（オートナンバー・・・長整数型）
主キー(2)発注コード（テキスト型）
　　　　　　　　※発注コードは『英数字』のためテキスト型。リンク先も『テキスト型』
(3)氏名（テキスト型）

【Ｂ】商品テーブル
(1)商品ID（オートナンバー・・・長整数型）
主キー(2)注文番号（数値型・・・倍精度浮動小数点型）
(3)品名（テキスト型）
(4)金額（通貨型）

【Ｃ】注文テーブル
主キー(1)注文ID（オートナンバー・・・長整数型）
　　　(2)日付
(3)発注コード（テキスト型）
　　　　　　　※発注コードは『英数字』のためテキスト型。リンク元も『テキスト型』

【Ｄ】注文明細テーブル（注文番号は全て4ケタの数字です。重複Noはありません）
主キー(1)注文明細ID(オートナンバー型・・・長整数型）
(2)注文ID（長整数型）
(3)注文番号1(数値型・・・長整数型)
(4)数量（注文番号1）
(5)注文番号2
(6)数量（注文番号2）
(7)注文番号3
(8)数量（注文番号3）
(9)注文番号4
(10)数量（注文番号4）
・
・
・※注文番号50まで続く・・・

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.1

Siegrune · ベストアンサー

一般的には、

【Ａ】顧客テーブル
主キー(1)顧客ID（オートナンバー・・・長整数型）
(2)氏名（テキスト型）
※同一顧客で複数の発注を受けたら同じ顧客IDを使うように考えます。
（発注コードは入れるべきでない）

【Ｂ】商品テーブル
主キー(1)商品ID（オートナンバー・・・長整数型）
(2)品名（テキスト型）
(3)単価（通貨型）・・・定価があるなら設ける。
※同一商品で複数の発注を受けたら同じ商品IDを使うように考えます。
（注文番号は入れるべきでない）

【Ｃ】注文テーブル
主キー(1)注文ID（オートナンバー・・・長整数型）
(2)日付
(3)発注コード（テキスト型）
　　　　　　　※発注コードは『英数字』のためテキスト型。リンク元も『テキスト型』
(4)顧客ID・・・顧客との紐付けはこの項目で行う。

【Ｄ】注文明細テーブル（注文番号は全て4ケタの数字です。重複Noはありません）

　主キー(1)注文明細ID(オートナンバー型・・・長整数型）
　(2)注文ID（長整数型）
または
　(1)注文ID（長整数型）
(2)枝番・・・1から連番
主キーは(1)と(2)のセット

(3)注文番号(数値型・・・長整数型)
(4)商品ID
(5)数量（注文番号1）
(6)単価（通貨型）・・・商品マスタの単価はいつでも同じではないので、注文と一致しないことあり。
金額は、数量×単価で求めるか、別途(7)金額を設けるか。
（金額設定の仕方によりけりで、3本1000円とかがあるなら、(7)金額も必要。）

注文明細テーブルは、「ひとり最大50個の商品を一枚の注文書で発注します」に対応して、
１枚の注文書で、最大50レコード作ります。

リレーション
【Ａ】(1)-【Ｃ】(4)
【Ｂ】(1)-【Ｄ】(4)
【Ｃ】(1)-【Ｄ】(1)か(2)

というふうにしますが。
（まずは、何でこういう設計にするのか、現状との違いから話をしていったほうが
　いいのかなと思います。
　⇒現状のほうがいいというのなら、それで話を進めてもいいのですが、
　　後の実績抽出は処理が面倒になると思っておいてください。）

お礼

大変ご丁寧な回答をありがとうございました。誰でも発注業務をできるように、発注コードと注文番号で発注書を起こせるようにエクセルで、ベースを設計しました。
また将来的には発注カテゴリー（発注書の種別）は3つあります。四ケタの数字で1000～1999まではＡという注文書。2000～2999はＢという注文書。それいがいの数字3000～9999まではＣという注文書に自動的に情報が流れるよう、アクセスで設計したいと考えております。
良い形にしたいのでまずは、実績処理の仕組みをご教授いただけると助かります。大きなフレームで考えて次につなげていきたいと思います。

補足

さっそくのご回答ありがとうございます。
通常のテーブルの設計方法について大変参考になりました。ありがとうございます。
なぜ、今回このような設計にしたのかを補足したいと思います。

■顧客テーブルに発注コードを入れていた理由の説明
ベースにしてたのはエクセルの発注書です。顧客は固定なので、発注業務を迅速におこなう為に「英数字」の会員番号をVLOOKUP関数を使い名前と商品の手配方法を、別のシートから参照させていました。※現時点での顧客というのは、当社の社員になります。所属グループが複数あり既存のコードを使わないと運用ができない母体があったため、『Ａ101』とか『Ｂ101』という会員番号を使わざる得ない理由になっています。

■商品テーブルと注文テーブルと注文番号をいれていた理由
これも同じくエクセルの商品テーブルに、4ケタの発注番号を重複しないように設けて、
別の注文書に数字を入力するだけで、商品名と商品名詳細が表示されるようVLOOKUP関数で参照するために設定していました。

まずは回答者様のやりかたを実直に行い、アクセスの理解を深め、考察しようと思います。
できれば、今のエクセルのデーターをインポートするだけで実績処理できるような形をイメージしていましたので、そのような方法ができるのであれば、ご教授くださると大変ありがたいです。