Microsoft Access 掲示板

場合分けについて / 1

14 コメント

views

4 フォロー

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

ソース表示通報 ...

りんご様

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

ある属性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	分類名
1	AA
2	AB
3	AC
4	AD

`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      |

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

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

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

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


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

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

英字分類のテーブル

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

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

数字分類のテーブル

数字分類
00 ※
01
02
…
97
98
99

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

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

クエリの途中経過

カラム	英字分類	数字分類	分類:[英字分類] & “-“ & [数字分類]
1	AA	01	AA-01
1	AA	02	AA-02
1	AA	03	AA-03
…	…	…	…
676	ZZ	97	ZZ-97
676	ZZ	98	ZZ-98
676	ZZ	99	ZZ-99

( 続き ) 理想は

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

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

`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 |

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

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

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

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

分類名	分類CD	規格
AA	1	S
AB	2	M
AC	3	L
AD	4	S
AD	5	M

となった場合、分類名が主キーではシステムが破綻します。


[理由3.]
私自身がサロゲートキー肯定派であるため。
( ナチュラルキーとサロゲートキーどちらも使います )

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

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

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

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

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

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

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

分類CD	分類名	フラグ
1	AA	1
2	AB	2
3	AC	2
4	AD	3
5	AD	4
6	AE	5

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

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

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

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

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

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

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

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

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

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

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

タグリスト

VBA (387) Office2016 (294) フォーム (242) Office2021 or 2019 (224) クエリ SQL (220) テーブル (139) レポート (128) Office2013 (57) Office2010 (50) Microsoft365 (47)

Markdown テーブル生成

Accessのテーブルやクエリのデータを貼り付ける場合は下記で、Markdown書式のテーブルに変換して貼り付けてください。

Markdown Tables generator

Microsoft Access 掲示板

場合分けについて / 1

最新トピック

タグリスト

Markdown テーブル生成