派遣で働くエンジニアのスキルアップを応援するサイト

PRODUCED BY RECRUIT

エンジニアへの挑戦状 #05 Accessのクエリで現在庫を見える化【解説】

この記事は、2022年12月23日に配信した「エンジニアへの挑戦状 #05  Accessのクエリで現在庫を見える化」の解説です。まだ問題を解いていない方は、こちらからご覧ください。

さて今回は、ビジュアルプログラミングでSQLを組み立てられるAccessの「クエリ」機能についての問題でした。テーブルやリレーションシップがどのように表現されるのか、正解はわかりましたか? 早速解説していきます。

正解は、【選択肢3】

問題は、Accessを使った冷蔵庫の「在庫管理」でした。3つのテーブルから、「いつ買った」「何の食材が」「現在いくつあるのか」を把握できるレコードセットが得られるデザインビューを選ぶもので、正解は、以下の【選択肢3】です。

ポイントは、「Stock」テーブルを基準とした外部結合であること、Null値をゼロとして扱うNz関数を使った計算式になっていることです。

それでは、この結果に到達するまでのAccessのビジュアルプログラミングの過程を見ていきましょう。

手順1 テーブルを外部結合する

まずは、デザインビューの上段に3つのテーブルを配置して、リレーションシップを設定します。リレーションシップは、フィールド同士をドラッグして重ねることで設定できます。その後、必要になるフィールドを下のグリッドへドラッグして配置します。データを把握するために、いったんフィールドを多めに取り出してみましょう。

こちらのクエリの実行結果(データシートビュー)は以下になります。

「stockID」に対して、いつ、いくつ買ったか、それをいつ、いくつ使ったかが見えるようになりました。

しかし、これでは情報が足りません。Accessのテーブル結合はデフォルトが内部結合(INNER JOIN)なので、「買ったけれど使っていない食材(feedIDを持たないレコード)」は表示されていないのです。

この問題を解決するには、「Stock」テーブルと「Feed」テーブルのリレーションシップを外部結合にします。リレーションの線をダブルクリックして、設定画面から「Stock」を基準の外部結合にします。外部結合は、基準となるテーブルから矢印が伸びている形の線になります。SQLでいうと、「LEFT OUTER JOIN」の形です。

これで、「買ったけれど使っていない食材(feedIDを持たないレコード)」が表示されました。

手順2 在庫のあるものだけに絞り込む

使い切った食材の情報は不要なので、「stockOut」をFalseのものだけに絞り込みます。SQLでいうと「WHERE Stock.stockOut = False」の意味です。

使い切った食材は表示されなくなりました。

手順3 現在庫フィールドを作る

「stockQty(買った数)」から「feedQty(使った数)」を減算して、「現在庫」を表示する演算フィールドを作りましょう。空いているフィールドを選んで、リボンの「クエリデザイン」タブの「ビルダー」から設定できるので、以下のように「演算フィールド名:式」の形で書きます。SQLでいうと「SELECT 式 AS 演算フィールド名」の意味です。

実行すると、以下の結果が得られます。いくつか計算されていないレコードがありますね。これは、買った食材をまだ使っていないレコードは、「feedQty(使った数)」が存在しないNull値なので、計算ができないためです。

演算フィールドの式に、Nz関数を使いましょう。この関数は、値がNullだった場合に指定した値を返すことができるので、ゼロを指定します。

これで「feedQty(使った数)」がNullの在庫も計算できました。

手順4 必要なフィールドのみ表示する

最後に、フィールドを整理しましょう。「表示」のチェック有無で、SQLでいう「SELECT ○, ○, …」の抽出フィールドを選ぶことができます。

右から2番目の「stockOut」フィールドは、SELECTするフィールドとしては不要ですが、WHEREの条件で使っているので、必要な項目です。左から3番目から6番目のフィールドは、SELECTで不要かつ、条件としても使っていないので、下段のグリッドから削除してしまっても大丈夫です。

最終的にはこのような形のデザインビューになりました。「stockID」や「itemID」の表示はお好みです。これで、以下の結果を得ることができました。「いつ買った」「どの食材が」「あといくつ残っている」のかが、一目でわかるようになりましたね。

いかがでしたか? Accessは一般的なデータベースシステムとは違った立ち位置で、手軽、かんたん、低コストで高機能なリレーショナルデータベースが利用できます。普通のファイルシステムなのでバックアップを確実にとるなどの対策は必要ですが、個人的・実験的に「意外と使えるな」と思っていただけたら嬉しいです。

【出題者】 今村 ゆうこ さん
Excel、Access等の書籍を執筆し、事務系プログラマー、ブロガー、ライターとして活動するワーキングマザー。著書のイラストや図解も手掛けている。WordPressを使った個人ブログ(https://ateitexe.com/)で技術情報を発信している。著書に『スピードマスター Accessデータベース 用語図鑑』『Accessデータベース 本格作成入門』『Excel & Access連携 実践ガイド』『Excel VBA ユーザーフォーム&コントロール 実践アプリ作成ガイド』(技術評論社)など。

▼今回解けた方も、解けなかった方も、他の挑戦状にチャレンジ!
エンジニアへの挑戦状 #01 プログラム修正問題
エンジニアへの挑戦状 #02 Pythonでお宝探し
エンジニアへの挑戦状 #03 テストデータ作成問題
エンジニアへの挑戦状 #04 新型ウイルス感染者数をSQLで抽出