Microsoft Access 掲示板

ある属性X( 1 )を決めると、他の属性Y( AA )の値が一意に決まる
ある属性X( 2 )を決めると、他の属性Y( AB )の値が一意に決まる
ある属性X( 3 )を決めると、他の属性Y( AC )の値が一意に決まる
...
場合、「 Y は X に関数従属している 」といいます。
Y の種類が少数の場合は
IIf関数やSwitch関数を使って X を定義するのもいいでしょうけど
Y の種類が多い場合や、今後、Y の種類が増えることが想定される場合は
テーブル設計を見直したほうがいいでしょう。

CREATE TABLE t_分類マスタ
(
      分類CD INT           NOT NULL PRIMARY KEY
    , 分類名 VARCHAR( 50 ) NOT NULL
);

      入力ID  COUNTER  NOT NULL PRIMARY KEY
    , 分類    VARCHAR( 50 ) NOT NULL
);

| 入力ID |  分類 |
|--------|:-----:|
| 1      | AA-01 |
| 2      | AB-01 |
| 3      | AB-02 |
| 4      | AA-02 |
| 5      | AA-03 |
| 6      | AC-01 |
| 7      | AB-03 |
| 8      | AA-04 |
| 9      | AB-04 |
| 10     | AB-05 |
　
だとすると

SELECT y.入力ID
     , y.分類
     , x.分類CD
FROM t_分類マスタ x
   , t_分類トラン y
WHERE x.分類名 = Left$( y.分類, 2 )
ORDER BY 1 ;

| 入力ID | 分類  | 分類CD |
|--------|-------|--------|
| 1      | AA-01 | 1      |
| 2      | AB-01 | 2      |
| 3      | AB-02 | 2      |
| 4      | AA-02 | 1      |
| 5      | AA-03 | 1      |
| 6      | AC-01 | 3      |
| 7      | AB-03 | 2      |
| 8      | AA-04 | 1      |
| 9      | AB-04 | 2      |
| 10     | AB-05 | 2      |

　この回答は、お遊びですので、軽く流して下さい。

テーブル1:フィールド1(短いテキスト型)
テーブル2:フィールド1(短いテキスト型)・・・テーブル1をコピペして下さい。

クエリ1(テーブル作成クエリ)

　
　テーブル作成クエリを実行したら、1列目にオートナンバー型のフィールド「カラム」を挿入して下さい。データシートビューに切り替えると、オートナンバリングが実行されます。

英字分類のテーブル

　同じ要領で、数字分類(短いテキスト型)のテーブルを作ります。

数字分類のテーブル

※最初のレコード、00は削除して下さい。

　最後に、英字分類テーブルをテーブル1、数字分類テーブルをテーブル2に見立て、同じ要領で進めて下さい。

クエリの途中経過

mayuさんに質問なんですが、なぜ分類CDがPRIMARY KEYなんでしょう？
文字に対して番号を割り当てるなら分類名側をPRIMARY KEYとしたほうがそれっぽいと思うのですが

hirotonさん、ご質問ありがとうございます。
分類CDをPRIMARY KEYとする理由を3つほど述べますね。

[理由1.]
PRIMARY KEY はソートに利用することが多いから、です。
文字列ですと、全行パディングされていない限りソートが難しくなります。

また、列数が２つのテーブルに
PRIMARY KEY と INDEX 両方を付与するのは
DBの容量増加、UPSERTの遅延、INDEX破損率の上昇
といったリスクを抱えることになりますので
列数の少ないマスタテーブルでは、設計上の理由から回避したいです。
( 経験則上、テーブルの破損 ＝ INDEXの破損 が圧倒的に多いです )
　
　
[理由2.]
( あくまで可能性として )分類の再編が発生して

サロゲートキーを利用する一因として
理由２.のように、主キーの体系が変化した場合などの影響が
ナチュラルキーに比べて( サロゲートキーのほうが )少なくなります。

んー、質問の内容に対応する場合と、新規で構築する場合とで話も変わってくるとは思うんですが>> 3の段階の話だと

こういう登録が許容されるテーブルを使うのはどうかと思うんですよね

改めて構築するということだと分類CDがどこまでサロゲートキーとして有効かというのもあって、可能性の話なら

もあり得るんじゃないでしょうか。質問上だと、「フラグ」というカラムにしたいということなので

この形でサロゲートキーな分類CDを使うならわかるんだけどという感じです

こういう登録が許容されるテーブルを使うのはどうかと思うんですよね

分類名の重複は、データの登録前後ともに、集計クエリ・DCountのような定義域集計関数で
誤りをチェックできるため、設計上問題ないという見解です。
　

質問上だと、「フラグ」というカラムにしたいということなので
この形でサロゲートキーな分類CDを使うならわかるんだけどという感じです

私とhirotonさんで質問文の解釈に違いがありそうです。

mabeeさんは、( おそらく )便宜上「フラグ」と表現されましたけど
通常、フラグという言葉は、立つ・折るという
二者択一に近い条件分岐や選択で用いられます。

分類を文字列だけでなく、数値でも表現したい とご希望なのは
並び替えの基準となる「識別子」が必要とお考えになったのではないか
と推測しました。

したがって、私は{ フラグ ＝ 識別子 }と解釈し、識別子を主キーとしています。
( 分類CD, 分類名, フラグ の3列構成にしない理由にもなっています )

また、余談にはなりますけど
分類をキーとした場合は、差別化を図るかどうかは別として
AA, Aa, aA, aa ＡＡ それぞれを異なる値として扱えません。
( バイナリ比較が必要になり、カラムも GUI では作成不可 )
( 差別化を図る場合は>> 3の回答自体が無効という盛大なブーメランですね )

数値型フィールドを識別子とすれば、全角半角大文字小文字を
同一の値で扱うことも差別化も容易で、高度なスキルも必要ありません。

常時バイナリ比較になり、経験の浅いDBAでも扱いが容易なことから
値の識別となる大小比較や結合・並び替えは、できる限り文字列より数値で実施するべき
というのが私のポリシーですので
>> 3でのSELECT文も、苦肉の策に近く、ベストプラクティスではない
というのが本音にはなりますね。