Access学習メモ 2-6. 失敗から学ぶ（クエリ演算）

【例題】選択クエリの作成

以下仕様に基づき、商品ごとの[理論在庫数](注)が[最小在庫数]を下回る商品を抽出のうえ、必要な[発注数量]・[仕入金額]を算出する選択クエリを作成しなさい。

(1) クエリ名は『仕入先別発注対象商品』とすること。

(2) 表示するフィールドは下記とすること。

① [仕入先ＩＤ]

② [仕入先名]

③ [電話番号]

④ [ＦＡＸ番号]

⑤ [商品ＩＤ]

⑥ [商品名]

⑦ [仕入単価（税抜）]

⑧ [最大在庫数]

⑨ [最小在庫数]

⑩ [理論在庫数]

⑪ [発注数量]

⑫ [仕入金額]

(3) [理論在庫数]のフィールドについては、下記の値を算出し設定すること。

●下記（ア）から（イ）を減算した値

（ア）「商品ＩＤ」ごとの「入出庫トラン」テーブル「入庫数」の合計値

（イ）「商品ＩＤ」ごとの「入出庫トラン」テーブル「出庫数」の合計値

(4) [発注数量]のフィールドについては、下記の値を算出し設定すること。

●[最大在庫数]から、上記(3) [理論在庫数]を減算した値

(5) [仕入金額]のフィールドについては、下記の値を算出し設定すること。

●[仕入単価（税抜）]に、上記(4) [発注数量]を乗算した値

(6) 抽出条件として、下記を設定すること。

●上記(3)[理論在庫数]が、[最小在庫数]を下回るレコードのみ抽出

(7) [仕入先ＩＤ]の昇順で、並び替えを設定すること。

(8) クエリ内の各テーブル間に適切な結合および結合プロパティが設定されていること。

学習メモ1-9.でひと通り作成

「例題⑪：発注数量」演算で失敗

【×】発注数量:Nz(Sum([最大在庫数]),0)

　　　-(Nz(Sum([入庫数]),0)-Nz(Sum([出庫数]),0))

【×】発注数量:Nz([最大在庫数],0)

　　　-(Nz(Sum([入庫数]),0)-Nz(Sum([出庫数]),0))

【○】発注数量:

　　　[最大在庫数]-(Nz(Sum([入庫数]),0)-Nz(Sum([出庫数]),0))

【○】理論在庫数:Sum([入庫数])-Sum([出庫数])

　　　発注数量:[最大在庫数]-Nz([理論在庫数],0)

Accessがクエリの計算式にerror発報する際、主役オブジェの主キーについて指摘するケースが見受けられるが、多くの場合はそれ以外に原因が潜む。

「最大在庫数」のように集計を必要としない要素に対しSum関数などの不適合を見つけると、Accessはerror判定を下す。

また、errorとまではいかないが、Null値を返す可能性のうすい要素をわざわざNz(,0)で包む必要はなかった。

例題では『サブフォーム内「入庫数」の合計から「出庫数」の合計を減算し[理論在庫数]を算出...』と明記されており、理論在庫数をサブクエリで別計上し、メインのクエリにて最大在庫数から減算する方法もある。

在庫数は商品ごとにとりまとめる（グループ化）必要があるため、サブクエリには商品IDを採用。

グループ化を行うには選択クエリ画面にて「集計Σ」キーを使用する。

演算式を投入する「理論在庫数」フィールドの「集計」行は「グループ」のままではなく「演算」に変更する。

式の注意点

クエリのフィールド（デザイングリッド）を使用する場合と、フォームやレポートのコントロールソースを使用する場合では微妙に勝手が違う。

（自分が慣れないだけだろうけど）Accessにはいろいろ落とし穴が多い。

命令や計算を実行するときに備える「予約語」と呼ぶ言語をAccessは持つ。

関数や演算子など多様な命令語がある中、フィールド名（項目名）がこれらと入り混ざることで、Accessは「これは名称か？命令か？」と混乱してしまう。

加えて、日本語のような全角文字はシステム内部的には特殊文字として扱われるため、「ここからここまでは開発者が勝手につけた名称だから気にしなくていいよ」とAccessにお知らせするために、[]を用いる必要がある。

記入しなくても問題にならなかったケースもあるけれど、基本的には備えておいてたぶん間違いないのかな。