Access学習メモ 7-11. SQL基礎学習⑪

INNER JOIN句を用いた検算方法（請求書）

管理職の代理で、請求書の内容チェック～決裁をおこなう必要が生じた。

私の人生にAccessは必要ない...と断言して早1ヶ月。

電卓を叩く工程だけでもAccessに任せられないものか、画面と睨めっこ中。

方法を調べる内、これまで取り組んできたINNER JOINの学習が活かせる可能性が生じた。

別々のオブジェクト(テーブルやクエリ)に存在する同一データのレコード(行)を探し当て

各オブジェクトの列(フィールド)をあたかもひとつのオブジェクト上のデータのように繋ぎ合わせることができる。

ココナラのユーザー登録を例に挙げると...

(会員IDを各テーブルの仲介役と仮定）

テーブル1: ココナラ登録会員リスト

テーブル2: ココナラブログ投稿歴のある会員リスト

テーブル3: ココナラサービス出品歴のある会員リスト

テーブル4: ココナラサービス購入歴のある会員リスト

それぞれのテーブルに、各条件を満たす会員IDが格納された状況下において

「ココナラに会員登録している」

かつ「ココナラブログを1度でも書いたことがある」

かつ「ココナラで何らかの商品を出品したことがある」

かつ「ココナラで何らかの商品を購入したことがある」

INNER JOIN句を用いたSQLにより命令を与えることで、

テーブル別に分類された上記条件をすべて満たす会員データを抽出することも可能。

INNER JOIN句のこうした働きを応用することで、

日頃は別々に運用されているテーブル同士またはテーブルをもとに作成されたクエリ同士を呼び集め

一定の条件下における演算にも展開できる・・・はず。

社内情報を社外に持ち出すわけにいかず、再びココナラを例に挙げて考える。

出品の経験がないため、ルールなど詳しいことはよく分からないが

出品者が自らの成果物に対し、販売価格を検討する際には

完成までにかかる経費や作業工数などを予測し

損益分岐点を下回らない程度の最低料金を下限として設定し

相応しい数字へと調整を行うのが自然な流れではと思う。

その考え方に基づき、リレー形式でクエリ(SQL)を組み請求金額を導き出してみる。

【例】

80円の商品を30こ販売しました。

制作のための所要時間は3時間。時給は1,000円。追加経費も少々。

この場合、請求価格として採用されるのは

単価と販売実績に基づく売上高か？

時給に基づく最低料金か？

Accessは正しく導き出してくれるでしょうか？

～①商品単価と販売件数に応じた売上高と②最低料金を算出～

① 販売額を計算

T_販売明細テーブル（テーブル = データを保存する場所）

販売単価＊販売件数

Q1_販売額集計クエリ（クエリ = テーブル上のデータを使って計算する場所）

② 最低料金を計算

T_最低料金テーブル（テーブル = データを保存する場所）

料金単価＊作業工数

Q2_最低料金集計クエリ（クエリ = テーブル上のデータを使って計算する場所）

～③：①②の算定結果を比べる～

Q1とQ2で、Job番号が同じレコード同士を結合(INNER JOIN)し、その後IIf関数で比較

【ポイント】

今回のINNER JOINは、

一般的に説明される「両テーブルに存在するデータ抽出」用途とは異なり、

比較対象となる①販売額と②最低料金を並列させることが目的である。

また、Job番号は現在、3レコードまで登録されているが

すべて情報が出揃っているJob番号01が今回の請求対象と判断される。

Q1.販売額の集計結果、

Q2.最低料金の集計結果のうち、

両方に存在するJob番号のデータだけを取り出し、

比較対象であるQ1とQ2を一覧化するために

INNER JOINを使用する。

～④：③で比較した①と②の計算結果の内、大きい数字の計算結果を価格として採用～

そして

～⑤：諸費用[追加料金]や[経費]があれば④に加算する～

Q3_採用価格判定SQL

SELECT

    Q1.[Job番号],

    Q1.[販売額],

    Q2.[最低料金],

    IIf(Q1.[販売額] > Q2.[最低料金], Q1.[販売額], Q2.[最低料金]) AS 採用価格,

    T_諸費用.[追加料金],

    T_諸費用.[経費]

FROM

    (

        Q1_販売額集計 AS Q1

        INNER JOIN Q2_最低料金集計 AS Q2 ON Q1.[Job番号] = Q2.[Job番号]

    )

    INNER JOIN T_諸費用 ON Q2.Job番号 = T_諸費用.Job番号;

～⑥：⑤の算定結果をもって請求額の確定～

Q4_採用価格(請求額)計算

SELECT

    Q3_採用価格判定.[Job番号],

    Q3_採用価格判定.[採用価格],

    Q3_採用価格判定.[追加料金],

    Q3_採用価格判定.[経費],

    [Q3_採用価格判定].[採用価格] + Nz ([追加料金], 0) + Nz ([経費], 0) AS 請求額

FROM

    Q3_採用価格判定;

【注意点】

SELECT句では、表示するフィールドや計算項目をカンマで区切ります。

IIf関数の構造

第1引数 条件式 , ←語尾にカンマ

第2引数 条件が真(TrueつまりYes)のとき　, ←語尾にカンマ

第3引数 条件が偽(FalseつまりNo)のとき　　←カンマは不要

クエリをまとめることもできなくはないが

敢えて計算過程を段階ごとに区切った方が、間違い探しには役立つ。

今回の例では、最低料金にて請求額を確定いたしました。