Access (一般機能)

Accessの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Access 2016)
テーブルの作り方について
投稿日時: 20/06/23 23:42:58
投稿者: rodemasu

こんばんわ、お世話になります、rodemasuです。
先日はどうもありがとうございました。
 
やっとクエリまではたどり着いたのですがかなり色々テーブルの作り方に疑問というか、私の作り方はおかしいんだろうなと思うところがあり再度書き込みさせていただきました。
 
今作っているのは
 
テーブル=基本情報
フィールド:主キー・氏名・住所・資格名・有無
主キーがオートナンバー型で番号、残りが「氏名」・「住所」・「資格名ID」・「漢検有無ID」・「簿記有無ID」になります。
 
テーブル=資格名
フィールド=主キー・資格名
主キーがオートナンバー型で名前が「資格名ID」
資格フィールドが資格名「漢検」、「簿記」
が入っています。
 
テーブル=漢検有無
フィールド=主キー・有無
主キーがオートナンバー型で名前が「簿記有無ID」
有無フィールドには「有」、「無」
が入っています。
 
この状態だと、資格がどんどん増えるごとに、○○有無テーブルがどんどん増えてしまいます。
 
うまくいえませんが・・・有無テーブルだけ別に作って、
Aさん「漢検」-「有」「簿記」-「無」
Bさん「漢検」-「無」「簿記」-「有」
・・・
というように、「有無」というテーブルを別に作って、それぞれの「漢検」や「簿記」と関連付けることは可能なのでしょうか?
 
ここの場に書くのがおこがましいような質問で申し訳ございません・・・

回答
投稿日時: 20/06/24 09:54:28
投稿者: sk

引用:
テーブル=基本情報
フィールド:主キー・氏名・住所・資格名・有無
主キーがオートナンバー型で番号、残りが「氏名」・「住所」・「資格名ID」・「漢検有無ID」・「簿記有無ID」になります。
  
テーブル=資格名
フィールド=主キー・資格名
主キーがオートナンバー型で名前が「資格名ID」
資格フィールドが資格名「漢検」、「簿記」
が入っています。

(以下、P は主キー)
 
[申込者基本情報]
----------------------------------------------------
P 申込者ID                オートナンバー型
  申込日時                日付/時刻型
  氏名                    テキスト型
  生年月日                日付/時刻型
  性別                    整数型またはテキスト型
  郵便番号                テキスト型
  住所                    テキスト型
  電話番号                テキスト型
  メールアドレス          テキスト型
----------------------------------------------------

[資格検定マスタ]
----------------------------------------------------
P 資格検定ID              オートナンバー型
  資格検定名称            テキスト型
  資格検定略称            テキスト型
----------------------------------------------------

[申込者取得資格検定情報]
----------------------------------------------------
P 申込者ID                長整数型
P 資格検定ID              長整数型
  取得日付                日付/時刻型
----------------------------------------------------

ざっくりとしたイメージとしては、以上のようなテーブル設計に
なさればよろしいのではないかと。

投稿日時: 20/06/24 20:37:11
投稿者: rodemasu

こんばんわ、rodemasuです。
丁寧なご回答どうもありがとうございます。
まだまだ私の知識不足で、何点かご教授いただけませんか。
 
[申込者取得資格検定情報]
----------------------------------------------------
P 申込者ID 長整数型
P 資格検定ID 長整数型
  取得日付 日付/時刻型
----------------------------------------------------
 
この部分ですが、主キーが2つ設定されているのがわかりませんでした。(主キーは1つしか無理という認識でしたので)
リレーショナルを組んでいるということでしょうか?
 
また、申込者IDと資格検定IDがオートナンバー型ではなく、長整数型になっているのも何故だかわかりませんでした。
 
大変恐縮ですがご教授いただければ助かります。

回答
投稿日時: 20/06/24 22:06:47
投稿者: MMYS

sk さんの考え方で良いと思います。
ただし、主キーはオートナンバー型はおすすめしません。
主キーはレコードを ユニーク にすることが目的です。
主キーは自前で番号を振るべきです。

> この部分ですが、主キーが2つ設定されているのがわかりませんでした。
> (主キーは1つしか無理という認識でしたので)

これは複合キーと言います。2つのレコードで主キーを表現します。
 
 
データベースの重要な概念で正規化があります。そして正規化の手法で必要なのが
・主キー
・外部キー
です。
 
主キーのことを PK と呼びます。(Primary key)
外部キーのことを FK と呼びます。 (Foreign key)
 
FKとは主キーに設定した上でマスターテーブルと 一対多 の参照整合性に設定しなさい。意味です。つまり、これでマスターテーブルに無いデータが登録出来ないとが『保証』されます。
 
 
以上を踏まえてskさんの設計を修正すると下記となります。

[申込者基本情報]
----------------------------------------------------
PK 申込者ID                長整数型
   申込日時                日付/時刻型
   氏名                    テキスト型
   生年月日                日付/時刻型
   性別                    整数型またはテキスト型
   郵便番号                テキスト型
   住所                    テキスト型
   電話番号                テキスト型
   メールアドレス          テキスト型
----------------------------------------------------

[資格検定マスタ]
----------------------------------------------------
PK 資格検定ID              長整数型
   資格検定名称            テキスト型
   資格検定略称            テキスト型
----------------------------------------------------

[申込者取得資格検定情報]
----------------------------------------------------
FK 申込者ID                長整数型
FK 資格検定ID              長整数型
   取得日付                日付/時刻型
----------------------------------------------------

 
考え方は以前レスした下記と全く同じです。
https://www.moug.net/faq/viewtopic.php?t=79523
 
テーブル名とフィールド名が違うだけで設計は完全に同じです。
『個人』テーブル → [申込者基本情報]
『質問』テーブル → [資格検定マスタ]
『回答』テーブル → [申込者取得資格検定情報]
 
 

投稿日時: 20/06/25 00:09:41
投稿者: rodemasu

こんばんわ、rodemasuです。
暖かいご回答どうもありがとうございます。
 
主キーと外部キーと考え方は理解できそうで出来ないような状況で考えています。
教えていただいたDB設計でそのまま打ち込んだら、うまくいきました。
ここから更に同じことを聞くようで申し訳ないのいですが、下記のDBがうまくいきません。
例えば、検定に「級数」がある場合です。
例えば漢検は1級から3級(適当です)
英検は1級から5級までが入るとします。
簿記も1級から3級といったように枝分かれする場合です。
以下のようなDB構成で行かないかなと考えてみました。
 
[申込者基本情報]
----------------------------------------------------
PK 申込者ID 長整数型
   申込日時 日付/時刻型
   氏名 テキスト型
----------------------------------------------------
 
[所持資格テーブル]
----------------------------------------------------
PK 所持資格ID 長整数型
  所持資格        テキスト型←漢検とか英検と簿記が入ります
----------------------------------------------------

 
[漢検所持級数テーブル]
----------------------------------------------------
PK 資格検定ID 長整数型
  資格級数        テキスト型←(1級〜3級)
----------------------------------------------------
 
[英検所持級数テーブル]
----------------------------------------------------
PK 資格検定ID 長整数型
  資格級数        テキスト型←(1級〜5級)
----------------------------------------------------
 
[簿記所持級数テーブル]
----------------------------------------------------
PK 資格検定ID 長整数型
  資格級数        テキスト型←(1級〜3級)
----------------------------------------------------

 
[申込者取得資格検定情報]
----------------------------------------------------
FK 申込者ID 長整数型
FK 所持資格ID 長整数型
FK 資格検定ID       テキスト型
----------------------------------------------------
 
これをどうリレーショナルすればいいのかがわかりません。
多分DB自体間違っているのだとも思います。
 
所持資格IDと資格級数フィールドのリレーショナルだと当然型も違うし、明後日なことをしてるんだなと自分でも思いますし、Accessでもエラーではじかれます。
かといって、所持資格IDと資格検定IDの1対多にしても、何が何に対して1対多なの頭では理解できても、Accessでは理解できないでしょう。
 
何時間も考えて試行錯誤を繰り返しています。
 
同じことばかり聞いているかもしれません・・・
なにかマスターテーブルを作らないといけないのでしょうか。
 
すいませんがご教授願えませんでしょうか。

回答
投稿日時: 20/06/25 11:55:40
投稿者: sk

引用:
例えば、検定に「級数」がある場合です。
例えば漢検は1級から3級(適当です)
英検は1級から5級までが入るとします。

[申込者基本情報]
----------------------------------------------------
PK 申込者ID                長整数型
   申込日時                日付/時刻型
   氏名                    テキスト型
----------------------------------------------------
 
[資格検定マスタ]
----------------------------------------------------
PK 資格検定ID              長整数型
   資格検定名称            テキスト型
----------------------------------------------------
("漢検","英検","簿記"などの資格、検定を定義)

[資格検定別等級マスタ]
----------------------------------------------------
PK 資格検定ID              長整数型
PK 資格検定等級ID          長整数型
   資格検定等級名称        テキスト型
----------------------------------------------------
("1級","準1級",2級"などの等級を資格、検定ごとに定義)

[申込者取得資格検定情報]
----------------------------------------------------
FK 申込者ID                長整数型
FK 資格検定ID              長整数型
FK 資格検定等級ID          長整数型
----------------------------------------------------

まず「資格ごとにテーブルやフィールドを作る」という発想を捨てて下さい

回答
投稿日時: 20/06/25 21:22:46
投稿者: MMYS

私なら、「資格検定マスタ」テーブルに下記のように入力します。
 

資格検定ID 資格検定名称
 1001   漢検1級
 1002   漢検2級
 1003   漢検3級
 1004   英検1級
 1005   英検2級
 1006   英検3級
 1007   英検4級
 1008   英検5級
 1009   簿記1級
 1010   簿記2級
 1011   簿記3級

資格検定名称フィールドには
・重複禁止
・NULL禁止
にします
 
重複禁止 にすることで二重登録を防ぎます。
未入力もありえないのでNULL禁止です。
 
 

投稿日時: 20/06/25 23:06:18
投稿者: rodemasu

こんばんわ、rodemasuです。
ずっとAccessとにらめっこしてます。
皆さま、アドバイスありがとうございます。
 

引用:

資格検定ID 資格検定名称
 1001   漢検1級
 1002   漢検2級
 1003   漢検3級
 1004   英検1級
 1005   英検2級
 1006   英検3級
 1007   英検4級
 1008   英検5級
 1009   簿記1級
 1010   簿記2級
 1011   簿記3級

 
なるほど、sk様の投稿と合わせてとても参考になります。
ただ、にらめっこしていて疑問というか私の知識不足で解読できなかった点として、例えば、
 
Aさんは 漢検1級・簿記1級
Bさんは 漢検2級・英検1級・簿記1級
 
を持っているといった場合、何処で判断等をするのかがわかりませんでした。
検定が10も20もある場合、全ての組み合わせを作ってユニークにすることは無理ですよね。
sk様のテーブル構成だと、何か解があるのかなと思って、テーブル・クエリとにらめっこしていましたが、分かりませんでした・・・。
 
多分PKが2つある[資格検定別等級マスタ]テーブルでユニークにしてるのかなと勝手に思ったりしています・・・。
 
何度も何度も申し訳ございませんが、ご教授いただければ助かります。

投稿日時: 20/06/25 23:47:25
投稿者: rodemasu

すいませせん、自コメントです。
色々触って考えていたら、とりあえずできました!
 
が、なぜできたのかはわかりません・・・

投稿日時: 20/06/26 00:39:49
投稿者: rodemasu

なんどもすいません、rodemasuです。
すいません、Accsessにこういう考え方はないのでしょうか。
色々調べていると、複数の主キーを用いる場合は、一つの主キーだけではユニークにならないものを、2つの主キーを用いてユニークにするという物(と認識しました)
 
  資格検定ID 長整数型
PK 資格検定等級ID 長整数型
   資格検定等級名称 テキスト型
 
にしつつ、
資格検定ID
が1の場合は漢検、2の場合には英検、3の場合には簿記といった形にして
 
資格検定ID 資格検定級数ID 資格検定名称
 1001   2001      漢検1級
 1001   2002      漢検2級
 1001   2003      漢検3級
 1002   2001      英検1級
 1003   2002      英検2級
 1003   2003      英検3級
 1003   2004      英検4級
 1003   2005      英検5級
 1004   2001      簿記1級
 1004   2002      簿記2級
 1004   2003      簿記3級
 
これならば、資格検定IDと資格検定級数IDで組み合わせるとユニークになるので、出来ないのかなと思った次第です・・・。
Accsessでこのようなテーブルを作って、リレーショナルをかけたら当然はじかれました。
 
単なる想像の世界ですが、方法はないのかなと思いました。[/quote]

回答
投稿日時: 20/06/26 05:51:37
投稿者: MMYS

リレーショナルデータベースでは「結合」です。
 
で、結合するには、そもそも、
結合しやすいデータ構造
・処理しやすいデータ構造
が必要です。
 
で、実務だと、たとえば、
・Aさんの所持資格は?
・簿記3級の資格保持者は何人いたっけ。
・去年1年間で漢検は何人合格の人数を出したい。
など。
 
例えば、去年1年間の合格者人数なら
・[資格検定マスタ]
・[申込者取得資格検定情報]
結合でレコードを集計きます。
 
 

回答
投稿日時: 20/06/26 10:09:26
投稿者: sk

引用:
色々調べていると、複数の主キーを用いる場合は、
一つの主キーだけではユニークにならないものを、
2つの主キーを用いてユニークにするという物(と認識しました)

画面設計、検索処理、集計処理、入出力処理などの要求仕様や、
そのシステムで扱う(ことが想定される)資格や検定の種類
(およびそれぞれの等級)の規模や件数を鑑みた上で、
「そうした方が都合が良い」なら
そういう形(複数フィールドインデックス)で主キーを
定義すればよいでしょうし、そうではないなら
単一フィールドインデックスを主キーに定義しても
構わないでしょう。
 
引用:
  資格検定ID 長整数型
PK 資格検定等級ID 長整数型
   資格検定等級名称 テキスト型

この場合は、各レコードの[資格検定等級ID]に
一意な値を格納するようにしなければなりません。
 
[資格検定等級ID]を「レコードを識別するための主キー」
(無意コード)として扱いながら、それぞれのレコードが
「どの資格/検定の」
「どのぐらいのグレードに当たる」
「何という名前の」
等級であるかを示す形で情報を管理したいのであれば、
例えば以下のようなテーブルを設計することになります。
 
[資格検定別等級マスタ]
-------------------------------------------------------------
PK 資格検定等級ID          長整数型(またはオートナンバー型)
FK 資格検定ID              長整数型
   等級                    長整数型
   資格検定等級名称        テキスト型
-------------------------------------------------------------

(レコードの格納例)
-------------------------------------------------------------
資格検定等級ID    資格検定ID    等級    資格検定等級名称
-------------------------------------------------------------
1                 1             1       日本漢字能力検定1級
2                 1             2       日本漢字能力検定準1級
3                 1             3       日本漢字能力検定2級
4                 1             4       日本漢字能力検定準2級
5                 1             5       日本漢字能力検定3級
6                 1             6       日本漢字能力検定4級
7                 1             7       日本漢字能力検定5級
8                 1             8       日本漢字能力検定6級
9                 1             9       日本漢字能力検定7級
10                1             10      日本漢字能力検定8級
11                1             11      日本漢字能力検定9級
12                1             12      日本漢字能力検定10級
13                2             1       実用英語技能検定1級
14                2             2       実用英語技能検定準1級
15                2             3       実用英語技能検定2級
16                2             4       実用英語技能検定準2級
17                2             5       実用英語技能検定3級
18                2             6       実用英語技能検定4級
19                2             7       実用英語技能検定5級
20                3             1       日商簿記1級
21                3             2       日商簿記2級
22                3             3       日商簿記3級
23                3             4       日商簿記初級
-------------------------------------------------------------

ただこういうレイアウトのテーブルを定義した場合、
[申込者取得資格検定情報]のレコードを編集するための
帳票フォーム(またはデータシートフォーム)を
設計する際には多少面倒なことになると思います。
 
いずれにしても、まずリレーショナルデータベースにおける
正規化リレーションシップの概念、
有意コード無意コードの違いと役割などについて
充分に学習してからテーブルの設計に取り掛かられることを
お奨めします。

投稿日時: 20/06/26 13:22:49
投稿者: rodemasu

こんにちわ、ろでますです。
sk様、何回も大変丁寧なご説明ありがとうございます。
 
この場では一点だけご確認させてください。
 
例えば、Aさんは漢検1級を持っている。
Aさんは英検1級も持っている。
 
という「判断」、つまりAさんのレコードが2つ以上出てきてもよいテーブルは
 

引用:

[申込者取得資格検定情報]
----------------------------------------------------
FK 申込者ID 長整数型
FK 資格検定ID 長整数型
FK 資格検定等級ID 長整数型
----------------------------------------------------

になると思ってよろしいでしょうか?
 
散発的な質問になってしまい申し訳ございません・・・。

回答
投稿日時: 20/06/26 14:11:26
投稿者: sk

rodemasu さんの引用:

例えば、Aさんは漢検1級を持っている。
Aさんは英検1級も持っている。
 
という「判断」、つまりAさんのレコードが2つ以上出てきてもよいテーブルは
 
引用:

[申込者取得資格検定情報]
----------------------------------------------------
FK 申込者ID 長整数型
FK 資格検定ID 長整数型
FK 資格検定等級ID 長整数型
----------------------------------------------------

になると思ってよろしいでしょうか?

概ねそういうことです。
 
つまり、[申込者基本情報]のある 1 件のレコードに対して、
そのレコードと同じ[申込者ID]を持つレコードが
[申込者取得資格検定情報]に 1 件以上格納される形になります。
 
何の資格を持っていない申込者については、
[申込者取得資格検定情報]に 1 件もレコードが
追加されない、ということになります。
 
[申込者基本情報]
----------------------------------------------------
申込者ID    申込日時      氏名
----------------------------------------------------
1           2020/06/01    Aさん
2           2020/06/01    Bさん
3           2020/06/02    Cさん
---------------------------------------------------- 

[申込者取得資格検定情報]
---------------------------------------------------- 
申込者ID    資格検定ID    資格検定等級ID
---------------------------------------------------- 
1           1             1
1           2             13
2           3             21
----------------------------------------------------

ただ、これもあくまで 1 つの例に過ぎません。
 
特に等級が設けられていない資格や検定もあるでしょうし、
また TOEIC のように取得スコアを直接入力するケースまで
想定するなら、また違った設計になるでしょう。

投稿日時: 20/06/26 15:23:15
投稿者: rodemasu

こんにちわ、rodemasuです。
 
とりあえず、今まで頂いた知識をベースに、以下の方法で苦し紛れにテーブルを作って、乗り切ってみました。
前提条件として、
漢検は等級の呼び方を「級」で表現し、1〜7級まで。
英検は等級の呼び方を「Level」で表現しLevel1〜5まで
簿記は等級の呼び方を「段」で表現し「1〜3段」まであるという前提にします。
 
[申込者基本情報]
----------------------------------------------------
PK 申込者ID 長整数型
   申込日時 日付/時刻型
   氏名 テキスト型
----------------------------------------------------
 
[申込者取得資格検定情報]
----------------------------------------------------
FK 申込者ID 長整数型
FK 資格検定ID 長整数型
FK 漢検検定等級ID 長整数型
FK 英検検定等級ID 長整数型
FK 簿記検定等級ID 長整数型
----------------------------------------------------
 
[資格検定マスタ]
----------------------------------------------------
PK 資格検定ID 長整数型
   資格検定名称 テキスト型
----------------------------------------------------
 
[漢検検定等級マスタ]
----------------------------------------------------
PK 漢検検定等級ID      長整数型
   等級名称     テキスト型(7レコードで1級〜7級)
----------------------------------------------------
 
[英検検定等級マスタ」
----------------------------------------------------
PK 英検検定等級ID      長整数型
   等級名称    テキスト型(5レコードでLevel1〜5)
----------------------------------------------------
 
[簿記検定等級マスタ」
----------------------------------------------------
PK 簿記検定等級ID      長整数型
   等級名称   テキスト型(3レコードでLevel1〜3段)
----------------------------------------------------
 
です。
 
こうすると、[申込者取得資格検定情報]はその人が持っていない資格級数の所は歯抜け(NULL)になるので、見にくくはなってしまいますが・・・。
 
何とかクエリでも情報はとることができました。
 
もっと勉強して頑張りたいと思います!。