Microsoft Access 掲示板

場合分けについて / 1

14 コメント
views
4 フォロー
1
りんご 2021/07/12 (月) 13:59:58 c564b@0e907

下記、どうでしょうか?
Switch関数 - もう一度学ぶMS-Access

通報 ...
  • 2

    りんご様

    返信ありがとうございます。
    switch関数で動くことは確認できたのですが、場合分けの数を増やしていったところ、16個ほどの条件で、指揮が複雑すぎます、というエラーが出るようになりました。
    他に手法はないのでしょうか?(そもそもaccessはそういうツールではないし、Excelでやればどうにかなるのですが…)
    ご教授いただけますと幸いです。
    よろしくお願いいたします。

    3
    mayu 2021/07/12 (月) 19:49:09 修正 ef559@a99f1 >> 2

    ある属性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
    );
    
    分類CD分類名
    1AA
    2AB
    3AC
    4AD
     
    `sql
    CREATE TABLE t_分類トラン
    (

          入力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      |
    
    7
    りんご 2021/07/12 (月) 22:18:20 c564b@0e907 >> 2

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

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

    フィールド1
    A
    B
    C
    X
    Y
    Z

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

    フィールド:フィールド1フィールド1英字分類:[テーブル1]![フィールド1] & [テーブル2]![フィールド1]
    テーブル:テーブル1テーブル2
    並び替え:昇順 昇順 
    表示:

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

    英字分類のテーブル

    カラム英字分類
    1AA
    2AB
    3AC
    674ZX
    675ZY
    676ZZ

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

    数字分類のテーブル

    数字分類
    00 ※
    01
    02
    97
    98
    99

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

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

    クエリの途中経過

    カラム英字分類数字分類分類:[英字分類] & “-“ & [数字分類]
    1AA01AA-01
    1AA02AA-02
    1AA03AA-03
    676ZZ97ZZ-97
    676ZZ98ZZ-98
    676ZZ99ZZ-99
  • 4
    mayu 2021/07/12 (月) 19:50:49 修正 ef559@a99f1 >> 1

    ( 続き ) 理想は

    CREATE TABLE t_分類マスタ
    (
          分類CD INT           NOT NULL PRIMARY KEY
        , 分類名 VARCHAR( 50 ) NOT NULL
    );
    
    分類CD分類名
    1AA
    2AB
    3AC
    4AD
     
    `sql
    CREATE TABLE t_分類トラン
    (

          入力ID     COUNTER  NOT NULL PRIMARY KEY
        , 分類CD     INT      NOT NULL
        , 分類別番号  INT
    );

    | 入力ID | 分類CD | 分類別番号 |
    |-------:|-------:|-----------:|
    | 1      | 1      | 1          |
    | 2      | 2      | 1          |
    | 3      | 2      | 2          |
    | 4      | 1      | 2          |
    | 5      | 1      | 3          |
    | 6      | 3      | 1          |
    | 7      | 2      | 3          |
    | 8      | 1      | 4          |
    | 9      | 2      | 4          |
    | 10     | 2      | 5          |
     
    

    SELECT y.入力ID
         , y.分類CD
         , y.分類別番号
         , x.分類名 & Format$( y.分類別番号, '-00' ) As 分類
    FROM       t_分類マスタ x
    INNER JOIN t_分類トラン y
            ON x.分類CD = y.分類CD
    ORDER BY 1 ;

    | 入力ID | 分類CD | 分類別番号 | 分類  |
    |--------|--------|------------|-------|
    | 1      | 1      | 1          | AA-01 |
    | 2      | 2      | 1          | AB-01 |
    | 3      | 2      | 2          | AB-02 |
    | 4      | 1      | 2          | AA-02 |
    | 5      | 1      | 3          | AA-03 |
    | 6      | 3      | 1          | AC-01 |
    | 7      | 2      | 3          | AB-03 |
    | 8      | 1      | 4          | AA-04 |
    | 9      | 2      | 4          | AB-04 |
    | 10     | 2      | 5          | AB-05 |
    
    9
    hiroton 2021/07/13 (火) 11:04:15 4339f@f966d >> 4

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

    10

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

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

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

    分類名分類CD規格
    AA1S
    AB2M
    AC3L
    AD4S
    AD5M
     
    となった場合、分類名が主キーではシステムが破綻します。
     
     
    [理由3.]
    私自身が サロゲートキー肯定派 であるため。
    ( ナチュラルキーとサロゲートキーどちらも使います )

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

    11
    hiroton 2021/07/13 (火) 14:00:58 4339f@f966d >> 4

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

    分類CD分類名
    1AA
    2AB
    3AC
    4AD
    5AD

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

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

    分類CD分類名
    1AA
    2AB
    3AC
    4AD
    4AE

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

    分類CD分類名フラグ
    1AA1
    2AB2
    3AC2
    4AD3
    5AD4
    6AE5

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

    13

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

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

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

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

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

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

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

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

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

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