はじめに

Accessの在庫管理テーブルでSQL実行を行うサンプルシナリオ(以降、本サンプルシナリオ)について説明します。

本サンプルシナリオの概要

本サンプルシナリオは、以下の処理を行います。

• Accessファイルの入庫データの値を在庫テーブルに挿入します。
• 在庫テーブルと出庫データを紐づけ、在庫テーブルの在庫数と当月出庫数を更新します。
• 在庫テーブルの値をExcelファイルに出力します。
• 出力したExcelファイルの体裁を整え、上書き保存しファイルを閉じます。
• 削除条件を選択し、在庫テーブルを削除します。
• Accessファイルを閉じます。

使用するWinActorモジュール

本サンプルシナリオでは、以下のWinActorモジュールを使用します。

• ノード/ユーザ/待機ボックス
• ノード/ユーザ/選択ボックス
• ノード/変数/変数値コピー
• ノード/変数/四則演算
• 01_WinActor制御/02_シナリオ制御/シナリオ停止.ums6
• 07_文字列操作/03_連結/文字列の連結（２つ）.ums6
• 18_Excel関連/01_ファイル操作/Excel操作(上書き保存).ums6
• 18_Excel関連/12_書式/Excelの書式設定.ums6
• プチライブラリ/Excelファイルの指定したシート内で行列サイズを自動調整するライブラリ
• プチライブラリ/Access操作(ファイルを保存なしで閉じる)
• プチライブラリ/Access操作(SQL文実行)
  (プチライブラリはこちらからダウンロードできます)

本サンプルシナリオの想定レベル

本ドキュメントは、以下の技術レベルのユーザを対象としています。

• 簡単なSQL文を作成したことがある。
• Accessでテーブルを作成したことがある。

動作確認環境

本サンプルシナリオは以下の環境で動作確認しています。

• Windows 10、WinActor 6.3.2、Microsoft Access 365、Microsoft Excel 365
• Windows 10、WinActor 7.3.1、Microsoft Access 365、Microsoft Excel 365

本サンプルシナリオの前提条件

• 本サンプルシナリオは納品データや出庫データがあらかじめAccessファイルに格納された状態であることを前提としています。
• 本サンプルシナリオはマイナス在庫を許容します。

本サンプルシナリオの使い方

本サンプルシナリオの実施方法は以下の通りです。

Step1.テーブルの準備

テーブルは以下のような形式で作成されています。

在庫テーブル
フィールド名：データ型

• 商品ID：数値[十進型]13桁　(主キー)
• 商品名：短いテキスト(255文字)
• 在庫数：数値型(既定値：0)
• 当月入庫数：数値型(既定値：0)
• 当月出庫数：数値型(既定値：0)
• 更新日時：日付/時刻型(既定値：Now())

本サンプルシナリオではデータが10件用意されています。

Step2.インポート準備

在庫テーブルに登録や更新を行うための入庫データと出庫データはAccessにインポートしてあります。
データを変更する場合はExcelファイルなどからインポートを行ってください。

Step3.WinActorで本サンプルシナリオを読み込み

WinActorを起動し、本サンプルシナリオを読み込みます。

Step4.動作に必要な変数の設定・変更

本サンプルシナリオを実行するために必要な値を、変数一覧で設定・変更します。
以下の変数を必要に応じて設定・変更してください。

• 在庫ファイルパス
  
  • 操作するAccessファイルを絶対パスまたは相対パスで指定してください。
    (例) 在庫管理ファイル.accdb
• INSERT_SQL
  
  • INSERTのSQL文を指定してください。
    (例) INSERT INTO 在庫テーブル(商品ID,商品名,在庫数,当月入庫数) SELECT 商品ID,商品名,在庫数,在庫数 FROM 入庫データ
• UPDATE_SQL
  
  • UPDATEのSQL文を指定してください。
    (例) UPDATE 在庫テーブル AS A INNER JOIN 出庫データ AS B ON A.商品ID = B.商品ID SET 在庫数 = 在庫数 - 出庫数, 当月出庫数 = 当月出庫数 + 出庫数
• SELECT_SQL
  
  • SELECTのSQL文を指定してください。
    (例) SELECT 商品ID, 商品名, 在庫数, 当月入庫数, 当月出庫数, 更新日時 FROM 在庫テーブル
• DELETE_SQL条件付き
  
  • 条件付きDELETEのSQL文を指定してください。
    (例) DELETE * FROM 在庫テーブル WHERE 当月出庫数 = 0
• DELETE_SQL全件
  
  • 全件DELETEのSQL文を指定してください。
    (例) DELETE * FROM 在庫テーブル
• EXCELファイル
  
  • 出力用EXCELファイル名を指定してください。
    (例) 在庫テーブル出力.xlsx

Step5.WinActorで本サンプルシナリオを実行

本サンプルシナリオを実行開始します。

Step6.サンプルシナリオ実行終了後の確認

InsertとUpdateの結果を出力したExcelファイルで確認してください。
在庫テーブルのデータが条件に従って削除されていることを確認してください。

トラブルシューティング

本サンプルシナリオでは、変数指定が不十分であった場合、エラーメッセージが出力されます。
詳細については、エラーメッセージ一覧を参照してください。

サンプルシナリオ解説

本サンプルシナリオで使用している実装テクニックについて説明します。

1.在庫テーブルへのデータ登録

入庫データから在庫テーブルへ在庫を登録しています。

「INSERT INTO 在庫テーブル(商品ID,商品名,在庫数,当月入庫数) SELECT 商品ID,商品名,在庫数,在庫数 FROM 入庫データ」

上記のSQLで複数のデータを一括して在庫テーブルに登録できます。
本サンプルシナリオはVALUES句で直接値を指定するのではなく「入庫データ」テーブルを読み込んでSELECTで値を取得することでVALUES句の代わりに値をセットしています。

VALUES句を指定することでデータを1件ずつ登録することもできます。

(例)
INSERT INTO 在庫テーブル (商品ID,商品名,在庫数,当月入庫数) VALUES (4900000000001, '商品データ001', 10, 10)
INSERT INTO 在庫テーブル (商品ID,商品名,在庫数,当月入庫数) VALUES (4900000000002, '商品データ002', 20, 20)

2.在庫テーブルのデータ更新

出庫データから在庫テーブルへ出庫数を更新しています。

「UPDATE 在庫テーブル AS A INNER JOIN 出庫データ AS B ON A.商品ID = B.商品ID SET 在庫数 = 在庫数 - 出庫数, 当月出庫数 = 当月出庫数 + 出庫数」

上記のSQLの通り、本サンプルシナリオでは在庫テーブルと出庫データの同じ主キーで結合することで、複数行のデータを一括更新しています。

■更新内容
　①在庫数(在庫テーブル)　　　：在庫数(在庫テーブル) - 出庫数(出庫データ)
　②当月出庫数(在庫テーブル)：当月出庫数(在庫テーブル) + 出庫数(出庫データ)

■SQL説明
　①UPDATE 在庫テーブル AS A　　　　　　：「A」は在庫テーブルの別名です。
　②INNER JOIN 出庫データ AS B　　　　　：「B」は出庫データの別名です。
　③ON A.商品ID = B.商品ID　　　　　　　　　：在庫テーブル(A)と出庫データ(B)で同じキーを持った値の行を結合します。
　④SET 在庫数 = 在庫数 - 出庫数,　　　　：在庫テーブルの在庫数から出庫データの出庫数を引いた値を在庫テーブルの在庫数にセットします。
　⑤当月出庫数 = 当月出庫数 + 出庫数　：在庫テーブルの当月出庫数から出庫データの出庫数を加えた値を在庫テーブルの当月出庫数にセットします。

■テーブル結合イメージ
　「INNER JOIN」は在庫テーブルと出庫データを結合する命令で、在庫テーブルの行に出庫データの同じ商品IDを持つ行を結合し、
　在庫テーブルと出庫データのどちらにも値が存在する行のみを対象とする命令です。
　SETでは1行内での値を使って計算された値を在庫テーブルのフィールドにセットします。

　(例)
　在庫テーブルに出庫データを連結させるイメージ
　　在庫テーブル　　　　　　　　　　　　　　　　　 　　　　　出庫データ
　　商品ID, 商品名, 在庫数, 当月入庫数, 当月出庫数, 更新日時 ＋　出庫日, 商品ID, 商品名,　出庫数
　〇4900000000001, 商品データ001, 10, 10, 0, 2021/03/18 15:00 ＋　2021年03月23日, 4900000000001, 商品データ001, 5
　〇4900000000002, 商品データ002, 20, 20, 0, 2021/03/18 15:00 ＋　2021年03月23日, 4900000000001, 商品データ002, 10
　×4900000000003, 商品データ003, 30, 30, 0, 2021/03/18 15:00 ＋　該当データなし
　〇4900000000004, 商品データ004, 40, 40, 0, 2021/03/18 15:00 ＋　2021年03月23日, 4900000000004, 商品データ004, 20

　上記の例では「〇」が先頭についているデータが結合されたデータで、更新対象のデータとなり、この行の値で加減算を行い該当フィールドに更新します。

■補足

　本サンプルシナリオではINNER JOINで直接更新値を記述するのではなく、「出庫データ」テーブルの行を結合して1行データを作成し、その値を使って更新しています。

　SETに更新値を直接指定することでデータを1件ずつ更新することもできます。

　(例)
　UPDATE 在庫テーブル SET 在庫数 = 5, 当月出庫数 = 5 WHERE 商品ID = 4900000000001
　UPDATE 在庫テーブル SET 在庫数 = 10, 当月出庫数 =105 WHERE 商品ID = 4900000000002

3.在庫テーブルからExcelファイル出力

在庫テーブルからExcelファイルを出力します。

「SELECT 商品ID, 商品名, 在庫数, 当月入庫数, 当月出庫数, 更新日時 FROM 在庫テーブル」

上記のSQLで抽出したデータをExcelファイルに出力し、書式設定と列幅の調整をしています。
商品IDの13桁の数値は、標準書式では13桁数値が正しく表示されないため、書式を変更しています。

SELECTの一例は以下の通りです。

(例)
SELECT * FROM 在庫テーブル WHERE 在庫数 <= 5 (在庫数が5以下)
SELECT 納品日, SUM(在庫数) AS 在庫数合計 FROM 入庫データ GROUP BY 納品日 (同じ納品日の在庫合計)

4.在庫テーブルのデータ削除

在庫テーブルのデータを、以下の選択した条件に従い削除します。

• 一部削除（当月出庫数が0のデータ）
  DELETE * FROM 在庫テーブル WHERE 当月出庫数 = 0
• 全件削除
  DELETE * FROM 在庫テーブル

DELETEの一例は以下の通りです。

(例)
DELETE * FROM 在庫テーブル WHERE 在庫数 = 0 AND 当月入庫数 > 0 AND 当月出庫数 > 0
(在庫数が「0」　且つ　当月入庫数が「0」より大きく　且つ　当月出庫数が「0」より大きい行を削除)