Access学習メモ 7-20. SQL基礎学習⑳

納税の義務化により、労働者は住民票のある自治体に対し、条件に応じ税金を納める必要がある。

「ふるさと納税」は、一定の手数料を支払うことで納税者みずから納付先自治体を選び

寄附という名目で住民税の一部を前払い(所定の控除申請が必要)できる制度である。

納税者にとっては、寄附への返礼として各地の特産品を受け取ることができ、人生の楽しみが増える。

（マイナと連携されてからは控除申請も便利になりましたね。）

納税者には人気の制度、自治体にとってはどうだろう。

Accessで作成したテーブル「受入額」：

「自治体に入ってきた寄附金総額」を示す。

自治体にとっての収益である。

Accessで作成したテーブル「控除額」：

「本来なら自治体が受け取るはずの住民税の内、他自治体への寄附により失った住民税」を示す。

自治体にとっての損失である。

受入額から控除額を差し引くことによって

「ふるさと納税によって、自治体は最終的にお金が増えた（流入超過）のか、減った（流出超過）のか」

税収への影響を可視化できる。

これまでの作業では「自治体リスト」と「受入額」＆「控除額」の各テーブルの自治体名(フィールドの値)に差異があったことから

計算のためのリレーションがむずかしい状況にあったので

名称を統一するためのSQLを追加した。

前記事でクエリを用いた各テーブルの自治体名の統一に成功したはずなので

次はそれぞれの自治体レコードに自治体IDを適用する段階に移ります。

まず、変更前後の確認用SQLを用いて

都道府県47件が全部表示されることを確認してから。

「自治体リスト」の都道府県と、「変換クエリ」で県や府を取り除いた都道府県をINNER JOINで重ね合わせ

47都道府県すべてがイコールで一致するか試す。

INNER JOIN（内部結合）とは、複数の異なるテーブルを、特定の共通する列(フィールドの値)を基準にして「結合（リレーション）」させ

1つの表のように繋ぎ合わせるための命令。

「都道府県」フィールドについて、自治体リストの表記と

「受入額」＆「控除額」の表記が異なっていれば、

INNER JOINの働きにより、当該レコードは結合から外されるため

SQL命令結果では、47都道府県が出力されず、異常を見つけることができる。

確認用クエリ

※フィールド名は[]で括らなくても問題ないことが多く、Accessによる自動形成でも[]抜きのケースが通常なのでここでは外してみた。

　ただ、今回の事例ではテーブル名とフィールド名の見分けがつきにくい点があるし

　エラーを心配するよりは、予め全フィールドに[]を記入するよう癖づけてもいいと思う。

▽「受入額」テーブルのチェック

SELECT

    自治体リスト.自治体ID,

    自治体リスト.都道府県,

    Q_受入額変換.都道府県,

    Q_受入額変換.都道府県変換

FROM

    自治体リスト

INNER JOIN

    Q_受入額変換

ON

    自治体リスト.都道府県 =

    Q_受入額変換.都道府県変換;

無事、47レコード(都道府県)表示されています。

▽「控除額」テーブルのチェック

SELECT

    自治体リスト.自治体ID,

    自治体リスト.都道府県,

    Q_控除額変換.都道府県,

    Q_控除額変換.都道府県変換

FROM

    自治体リスト

INNER JOIN

    Q_控除額変換

ON

    自治体リスト.都道府県 =

    Q_控除額変換.都道府県変換;

無事、47レコード(都道府県)表示されています。

「都道府県」フィールドの統一が確認できたら

次に「受入額」＆「控除額」各テーブルの「自治体ID」フィールドに

「自治体リスト」テーブルID情報のUPDATE文(更新用のSQL)を作る。

SQLの目的：

「受入額」テーブルの空っぽの「自治体ID」列に、都道府県名を頼りに引用した「自治体リスト」のIDを一括入力」 を命令

更新用クエリ

※やはり落ち着かないので、フィールド名に[]をつけることにした。

▽「受入額」テーブル「自治体ID」フィールド名にIDを一括反映

（INNER JOIN案）

UPDATE

    受入額

INNER JOIN

    自治体リスト

ON

    自治体リスト.[都道府県] =

    Replace(

        Replace(

            Replace(受入額.[都道府県],"東京都","東京"),

        "県",""),

    "府","")

SET

    受入額.[自治体ID] = 自治体リスト.[自治体ID];

▽「控除額」テーブル「自治体ID」フィールド名にIDを一括反映

（INNER JOIN案）

UPDATE

    控除額

INNER JOIN

    自治体リスト

ON

    自治体リスト.[都道府県] =

    Replace(

        Replace(

            Replace(控除額.[都道府県],"東京都","東京"),

        "県",""),

    "府","")

SET

    控除額.[自治体ID] = 自治体リスト.[自治体ID];

・・・の予定だったが、失敗に終わる。

受入額テーブルのIDは空欄のまま。

控除額テーブルも同じ。

---

Access SQL は ON 句に関数を書くことを公式にサポートしていない。

文法上は書けるが、最適化できずJOINが正しく評価されないようだ。

Microsoftの公式説明資料

INNER JOIN 操作（内部結合）

対象： 

共通のフィールドに一致する値がある場合に、2つのテーブルからレコードを結合します。

構文： 

FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2

INNER JOIN 操作は、次の要素で構成されます。

table1, table2：

レコード結合元テーブルの名前。

field1, field2：

結合フィールド(列)の名前。

数値型ではない場合、これらのフィールドは同じデータ型で

かつ同じ種類のデータを含んでいる必要がありますが

フィールド名自体が同じである必要はありません。

compopr：

任意の比較演算子: =, <, >, <=, >=, または <>。

---

価額2万ちょっとするソフトウェアなんだから、取説つけてほしいのね。

要するに、Microsoft社がAccessのINNER JOINに制限を設けているらしい。

ONの後ろには「純粋なフィールド名」しか書けない。

ONの結合条件は必ず以下の形で指定することと定められている。

table1.field1 （テーブル1のフィールド名）

compopr （比較演算子、例えば =）

table2.field2 （テーブル2のフィールド名）

比較する両側は、加工されていない「フィールド名そのもの」でなければならないという制約。

エラーになってしまった元のSQLの ON の後ろは以下。

左側：自治体リスト.[都道府県] 

右側：Replace(Replace(Replace(受入額.[都道府県]...))) 

AccessSQL仕様において、ONの左右にReplaceなどの関数や加工式を加えることが、構文ルールに違反しているため

Accessは正しいフィールド名として結合処理を行うことができず、

INNER JOINの中にReplace命令を組み込むことが間違っている、機能しないという結論になってしまう。

【修正案】

WHERE句を使う

WHEREの後ろには、INNER JOINのような「純粋なフィールド名でなければならない」厳しい制限はない。

WHEREはもともと「計算」や「関数の処理」を自由に行える場所だしね。

▽「受入額」テーブル「自治体ID」フィールド名にIDを一括反映

（WHERE案）

UPDATE 

    受入額, 

    自治体リスト

SET 

    受入額.[自治体ID] = 自治体リスト.[自治体ID]

WHERE 

    自治体リスト.[都道府県] = 

    Replace(

      Replace(

        Replace(受入額.[都道府県], "東京都", "東京"), 

                                "県", ""), 

                            "府", "");

Access更新クエリ画面で、「実行」ボタンを探してぽちっ。

「実行」ボタンがリボンに見当たらないときは、クエリデザインをクリックし

適当に新規クエリ作成をでっち上げるとリボンが自動的に切り替わって

「実行」が選べるようになる。

こういうのも何とかなるといいよね。

やっとこ「実行」を押すと、いつもの警告画面が出る。「はい」をぽちっ。

シーン...なんか喋ってくれると嬉しいんだけどな。

おそるおそるテーブルを開く。

反映されたようです。よかったよかった。

うまくいったので、控除テーブルも同様に。

▽「控除額」テーブル「自治体ID」フィールド名にIDを一括反映

（WHERE案）

UPDATE 

    控除額, 

    自治体リスト

SET 

    控除額.[自治体ID] = 自治体リスト.[自治体ID]

WHERE 

    自治体リスト.[都道府県] = 

    Replace(

      Replace(

        Replace(控除額.[都道府県], "東京都", "東京"), 

                                "県", ""), 

                            "府", "");

Access更新クエリ画面で、「実行」ボタンをぽちっ。

警告画面が出る。「はい」をぽちっ。

こちらにも無事、自治体IDが反映された。

自治体IDを搭載した３つのテーブルが出揃った。

せっかくなので「自治体ID」をリレーションシップでつなぎ合わせておく。

設定の際、テーブル本体は事前に閉じておく必要がある。

まずは受入額テーブルと自治体リストテーブルの「自治体ID」を結ぶ。

次に、控除額テーブルと自治体リストテーブルの「自治体ID」結ぶ。

名称とデータ型が同じであれば、リレーション自体は設定できるが

参照整合性は一対多の関係にしか成立しない。

今回の事例では、受入額テーブルと控除額テーブル同士では参照整合性を設定できない点に注意。

データを連携させる場合、片側のテーブルが絶対に重複しないレコード値を持つ「マスタテーブル(一側)」である必要があるから。

今回、用いられた受入額テーブルも控除額テーブルも

トランザクションテーブル(多側)同士であるため、互いを参照させた場合

Accessは「どちらのフィールドを基準にしてデータを整合すればいいのか分からない」状態になり、保証関係を結ぶことができない。

リレーションが設定されたら、フィールドに＋マークが表示される。

次回はいよいよ本題である収支集計のためのSQLを作ってみる。

長っ!!

Accessを使ってプログラム作りに挑戦するということはですね

つまりこういうことなんです。

ちゃんとしたエンジニアが雇われている職場の業務時間内における

非エンジニアによるシステム構築は、果たして称賛されるものなのか。

上司はそういうことに反対の立場です。

会社にとっては、人件費の二重払いにもなりかねない。

仕事をサボっているわけじゃないから止めるわけにもいかないが

できれば本来の担当業務に集中してもらいたいのが本音だろう。

「作った人が退職すれば、使えなくなっちゃうやつよね」

といったお約束の台詞をよく耳にするが、

明らかに「使う」気がなさそうな人ほど、そう発言してる印象。

Accessに限らず、物事の批判はとりあえず先ず試してみてから。

意見と偏見は別物。

Accessから学ぶことは多いです。

工程ごとの道具が備わっていて、作ったその場で結果を確認できますからね。