Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Access 2016)
特定の条件でテーブルの上から順番に計算したいです。
投稿日時: 20/06/19 16:41:04
投稿者: ぽろんちょ

いつもお世話になっております。
 
下記のようにそれぞれの品目コードの各製造ロットからいくつ出荷されたかを計算したいです。
テーブルは以下のようになります。
 
品目コード    製造ロット    区分    在庫入庫数量    出荷数
AAAAA         202005C    在庫       20    170
AAAAA         202006A    入庫       30    170
AAAAA         202006A    在庫       300    170
BBBBB         202006A    在庫       400    200
BBBBB         202006B    入庫       400    200
CCCCC         202006A    在庫       100    90
 
条件として製造ロットが古い方から出荷します。末尾のアルファベットはABCの順で古くなります。
製造ロットが同じだった場合には区分フィールドの入庫からさきに出荷します。
結果として以下のようなクエリで一番右側の「出荷数」フィールドを作成して、
各製造ロットからいくつ出荷したかを表示したいです。
 
品目コード    製造ロット    区分    在庫入庫数量    出荷数    出荷数 
AAAAA         202005C    在庫       20       170    20
AAAAA         202006A    入庫       30       170    30
AAAAA         202006A    在庫       300       170    120
BBBBB         202006A    在庫       400       200    200
BBBBB         202006B    入庫       400       200    0
CCCCC         202006A    在庫       100       90    90
 
色々と考えましたが、うまく導き出せませんでした。
どなたかお分かりになる方、教えていただけますでしょうか。
宜しくお願い致します。
 

回答
投稿日時: 20/06/19 18:08:24
投稿者: sk

引用:
品目コード    製造ロット    区分    在庫入庫数量    出荷数
AAAAA         202005C    在庫       20    170
AAAAA         202006A    入庫       30    170
AAAAA         202006A    在庫       300    170
BBBBB         202006A    在庫       400    200
BBBBB         202006B    入庫       400    200
CCCCC         202006A    在庫       100    90

それぞれの[品目コード]グループにおける[出荷数]の値が
グループ内の全てのレコードにおいて同じ値になっていますが、
これは「[製造ロット]ごとの出荷数」ではなく、
「その商品の([品目コード]ごとの)受注数量」を表しているのでしょうか。
(もしそうなら、テーブル設計にやや不安を覚えますが)
 
引用:
条件として製造ロットが古い方から出荷します。
末尾のアルファベットはABCの順で古くなります。
製造ロットが同じだった場合には区分フィールドの入庫からさきに出荷します。

また、[品目コード]が "AAAAA" であるグループについて、
3 件目のレコードの[在庫入庫数量]の値( 300 )が
その計算に組み込まれていないようですが、
それはどういった理由からでしょうか。

投稿日時: 20/06/22 11:09:35
投稿者: ぽろんちょ

sk様
  
早速の回答ありがとうございました。
  
引用:
それぞれの[品目コード]グループにおける[出荷数]の値が
グループ内の全てのレコードにおいて同じ値になっていますが、
これは「[製造ロット]ごとの出荷数」ではなく、
「その商品の([品目コード]ごとの)受注数量」を表しているのでしょうか。
(もしそうなら、テーブル設計にやや不安を覚えますが)
 
  
本来は下記のように出荷、在庫、入庫それぞれのテーブルが存在します。
出荷テーブルには製造ロットフィールドはありません。
(これをクエリで繋げた方が欲しいデータが得られるんじゃないかと思って作ってしまいました。)
  
出荷テーブル
品目コード 出荷数
AAAAA      170
BBBBB      200
CCCCC      90
  
在庫テーブル
区分 品目コード 製造ロット ケース数量
在庫   AAAAA   202005C 20
在庫   AAAAA   202006A 300
在庫   BBBBB   202006A 400
在庫   CCCCC   202006A 100
  
入庫テーブル
区分 品目コード 製造ロット ケース数量
入庫   AAAAA   202006A 30
入庫   BBBBB   202006B 400
  
  
引用:
また、[品目コード]が "AAAAA" であるグループについて、
3 件目のレコードの[在庫入庫数量]の値( 300 )が
その計算に組み込まれていないようですが、
それはどういった理由からでしょうか。
 
  
品目コード 製造ロット 区分 在庫入庫数量 総出荷数 ロット毎出荷数 
AAAAA      202005C 在庫    20    170    20
AAAAA      202006A 入庫    30    170    30
AAAAA      202006A 在庫    300    170    120
  
AAAAAの総出荷数170に対して製造ロットと区分の優先順位を考慮した上で、
ロット毎出荷数を上から20+30+120と必要数量を表示させたいです。
(最初の質問のデータに出荷数フィールドが2つありましたので訂正しました。申し訳ありません。)
  
分かりにくくて大変申し訳ありませんが、宜しくお願い致します。

回答
投稿日時: 20/06/22 14:00:23
投稿者: sk

引用:
本来は下記のように出荷、在庫、入庫それぞれのテーブルが存在します。
出荷テーブルには製造ロットフィールドはありません。

引用:
AAAAAの総出荷数170に対して製造ロットと区分の優先順位を考慮した上で、
ロット毎出荷数を上から20+30+120と必要数量を表示させたいです。

1. 以下のユニオンクエリ(仮に[入庫在庫ユニオンクエリ]とする)を作成する。
 
( SQL ビュー)
---------------------------------------------------------------------
SELECT [入庫テーブル].[品目コード], 
       [入庫テーブル].[製造ロット], 
       1 AS [区分コード], 
       [入庫テーブル].[区分], 
       [入庫テーブル].[ケース数量]
FROM [入庫テーブル] 
UNION ALL 
SELECT [在庫テーブル].[品目コード], 
       [在庫テーブル].[製造ロット], 
       2 AS [区分コード], 
       [在庫テーブル].[区分], 
       [在庫テーブル].[ケース数量]
FROM [在庫テーブル] 
ORDER BY [品目コード], 
         [製造ロット], 
         [区分コード];
---------------------------------------------------------------------

2. 更に以下の選択クエリを作成する。
 
( SQL ビュー)
---------------------------------------------------------------------
SELECT [入庫在庫ユニオンクエリ].[品目コード], 
       [入庫在庫ユニオンクエリ].[製造ロット], 
       [入庫在庫ユニオンクエリ].[区分コード], 
       [入庫在庫ユニオンクエリ].[区分], 
       [入庫在庫ユニオンクエリ].[ケース数量] AS [入庫在庫数量], 
       Nz((SELECT Sum(tmp.[ケース数量]) 
           FROM [入庫在庫ユニオンクエリ] tmp
           WHERE tmp.[品目コード] = [入庫在庫ユニオンクエリ].[品目コード] 
             AND (   (tmp.[製造ロット] < [入庫在庫ユニオンクエリ].[製造ロット])
                  OR (    tmp.[製造ロット] = [入庫在庫ユニオンクエリ].[製造ロット] 
                      AND tmp.[区分コード] < [入庫在庫ユニオンクエリ].[区分コード]))),0) AS [前回までの累計ケース数量], 
        Switch([前回までの累計ケース数量]>[出荷テーブル].[出荷数], 
               0, 
               [前回までの累計ケース数量]+[入庫在庫ユニオンクエリ].[ケース数量]>[出荷テーブル].[出荷数], 
               [出荷テーブル].[出荷数]-[前回までの累計ケース数量], 
               True, 
               [ケース数量]) AS [ロット毎出荷数], 
        [出荷テーブル].[出荷数] AS [総出荷数] 
FROM [入庫在庫ユニオンクエリ] 
INNER JOIN [出荷テーブル] 
ON [入庫在庫ユニオンクエリ].[品目コード] = [出荷テーブル].[品目コード] 
ORDER BY [入庫在庫ユニオンクエリ].[品目コード], 
         [入庫在庫ユニオンクエリ].[製造ロット], 
         [入庫在庫ユニオンクエリ].[区分コード];
---------------------------------------------------------------------

以上のクエリのような結果を得たい、ということでしょうか。

投稿日時: 20/06/23 11:11:35
投稿者: ぽろんちょ

sk様
 
返信ありがとうございます。
回答通りのSQLで作成したところ、求めていたデータを表示することができました。
 
最後に差し支えなければアドバイスをいただきたいのですが、
今回のような長くて複雑なSQLを書くにあたって、何かテクニックというか、
考え方のロジックのようなものはあるのでしょうか?
恥ずかしながら長いSQLになると、途端に頭が混乱してきます。
 
お忙しいところ大変申し訳ございませんが、アドバイスいただけると助かります。
宜しくお願い致します。

回答
投稿日時: 20/06/24 10:29:37
投稿者: sk

引用:
今回のような長くて複雑なSQLを書くにあたって、何かテクニックというか、
考え方のロジックのようなものはあるのでしょうか?

具体的な計算の流れや細かい手順を、フローチャートに書くなり、
箇条書きするなりなさればよいと思います。
(個々の処理にだけ注目すれば、あまり大したことはしていません)
 
引用:
恥ずかしながら長いSQLになると、途端に頭が混乱してきます。

私が挙げたのはあくまで解決策の 1 つに過ぎません。
SQL でも実現できる方法があったため、その一例を挙げただけです。
 
例えば、ワークテーブルと Recordset オブジェクトを使って
同じ結果を得る方法もありますし、そちらの方が処理の流れを
イメージしやすいということであれば、そちらの方法を
検討されてみてもよいでしょう。

投稿日時: 20/06/25 11:11:26
投稿者: ぽろんちょ

sk様
 
ご返信ありがとうございます。
今後は頂戴したアドバイスを意識しながらコードを書いていきたいと思います。
お忙しいところ、ありがとうございました。