Microsoft Access 掲示板

Microsoft Access 掲示板

クエリが複雑すぎます

18 コメント
views
4 フォロー

商品の在庫管理のシステムを作ろうと思っています

仕入明細のテーブルから、仕入数量と金額を月別で集計するクエリを作成　「q在庫表仕入」
売上明細のテーブルから、売上数量を月別で集計するクエリを作成　「q在庫表売上」

上記クエリから、月次移動平均で在庫金額を求めるために下記クエリを作成

SELECT
 q在庫表仕入.商品ID,
 q在庫表仕入.商品名,
 q在庫表仕入.期首数量,
 q在庫表仕入.期首金額,
 q在庫表仕入.[7月仕入数量],
 q在庫表仕入.[7月仕入金額],
 q在庫表売上.[7月売上数量],
 IIf([7月売上数量] Is Null,Null,(Nz([q在庫表仕入].[期首金額],0)+Nz([7月仕入金額],0))/(Nz([q在庫表仕入].[期首数量],0)+Nz([7月仕入数量],0))*[7月売上数量]) AS 7月払出金額,
 [q在庫表仕入]![期首数量]+Nz([q在庫表仕入]![7月仕入数量],0)-Nz([7月売上数量],0) AS 7月数量,
 [q在庫表仕入]![期首金額]+Nz([q在庫表仕入]![7月仕入金額],0)-Nz([7月払出金額],0) AS 7月金額

以下8月～6月まで繰り返し

FROM q在庫表仕入 INNER JOIN q在庫表売上 ON q在庫表仕入.商品ID = q在庫表売上.商品ID;

このクエリだと半年分くらいまでは上手く表示されるのですが、1年分で実行しようとすると「クエリが複雑すぎます」とエラーになってしまいます

クエリを二つに分けて作成もしてみましたが、2つ目のクエリで同様に複雑すぎますとエラーになってしまいました

何か改善する方法がありましたら教えてください

tama
作成: 2020/03/30 (月) 13:22:58
通報 ...
1
hatena 2020/03/31 (火) 15:36:16

元のテーブルの構成は不明なので、推測で回答するしかないですが、
クエリを追加クエリにしていったんテーブルに出力してから、そのテーブルをもとにクエリを作成するとうまくいくかも知れません。場合によってはその方が高速な処理になる場合もあります。

2
tama 2020/04/01 (水) 09:38:30 ca109@60c58

返信ありがとうございます。

テーブル等省略してしまってすみません。

追加クエリにすると前月以前のデータを変更した場合でも上手く動くのでしょうか、
商品マスタに各月の在庫数量と在庫金額を更新クエリで入力させれば良いのかとも思ったのですが
やり方がよく分からず・・

お手数ですが教えてもらえないでしょうか。

よろしくお願いいたします。

t決算日
決算日　2020/6/30

t商品マスタ
blob:https://zawazawa.jp/2d95d575-a70c-49d7-9f0c-52d99b7e4e45
t仕入明細
blob:https://zawazawa.jp/92a252de-14c8-4f2e-b55f-c99094e192fd
t売上明細
blob:https://zawazawa.jp/5606e247-2a8b-40d3-80ed-70c928cee4b7

q仕入明細
SELECT
 t仕入明細.仕入日,
 t仕入明細.仕入先ID,
 t仕入明細.仕入商品ID,
 t仕入明細.仕入数量,
 t仕入明細.仕入単価,
 [仕入数量]*[仕入単価] AS 仕入金額
FROM t仕入明細;

q売上明細
SELECT
 t売上明細.売上日,
 t売上明細.得意先ID,
 t売上明細.売上商品ID,
 t売上明細.売上数量,
 t売上明細.売上単価,
 [売上数量]*[売上単価] AS 売上金額
FROM t売上明細;

3
tama 2020/04/01 (水) 09:40:47 ca109@60c58

以下続き

q在庫表仕入
SELECT
 t商品マスタ.商品ID,
 t商品マスタ.商品名,
 t商品マスタ.期首数量,
 t商品マスタ.期首金額,
 Sum(IIf([q仕入明細]![仕入日] Between DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-11,0)+1 And DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-10,0),[q仕入明細]![仕入数量])) AS 7月仕入数量,
 Sum(IIf([q仕入明細]![仕入日] Between DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-11,0)+1 And DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-10,0),[q仕入明細]![仕入金額])) AS 7月仕入金額,
 Sum(IIf([q仕入明細]![仕入日] Between DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-10,0)+1 And DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-9,0),[q仕入明細]![仕入数量])) AS 8月仕入数量,
 Sum(IIf([q仕入明細]![仕入日] Between DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-10,0)+1 And DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-9,0),[q仕入明細]![仕入金額])) AS 8月仕入金額,
・・・・・以下繰り返し

FROM t決算日, t商品マスタ INNER JOIN q仕入明細 ON t商品マスタ.商品ID = q仕入明細.仕入商品ID
GROUP BY t商品マスタ.商品ID, t商品マスタ.商品名, t商品マスタ.期首数量, t商品マスタ.期首金額;

q在庫表売上
SELECT
 t商品マスタ.商品ID,
 t商品マスタ.商品名,
 t商品マスタ.期首数量,
 t商品マスタ.期首金額,
 Sum(IIf([q売上明細]![売上日] Between DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-11,0)+1 And DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-10,0),[q売上明細]![売上数量])) AS 7月売上数量,
 Sum(IIf([q売上明細]![売上日] Between DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-10,0)+1 And DateSerial(Year([t決算日]![決算日]),Month([t決算日]![決算日])-9,0),[q売上明細]![売上数量])) AS 8月売上数量,
 ・・・・・以下繰り返し
FROM t決算日, q売上明細 INNER JOIN t商品マスタ ON q売上明細.売上商品ID = t商品マスタ.商品ID
GROUP BY t商品マスタ.商品ID, t商品マスタ.商品名, t商品マスタ.期首数量, t商品マスタ.期首金額;

4
tama 2020/04/01 (水) 09:47:00 ca109@60c58

画像のアップロードができていなかったので、改めて載せます

画像1
画像2
画像3

5
hiroton 2020/04/01 (水) 16:17:15 修正 fb5a6@f966d

元データをうまいことくっつけてから集計は最後にやるのがいいんじゃないかなぁと

SELECT 商品ID, Max(q.商品名) AS 商品名, Sum(IIf(月=-1,仕入数量)) AS 期首数量, Sum(IIf(月=-1,仕入金額)) AS 期首金額,
 Sum(IIf([月]=0,[仕入数量])) AS 7月仕入数量, Sum(IIf([月]=0,[仕入金額])) AS 7月仕入金額, Sum(IIf([月]=0,[売上数量])) AS 7月売上数量, ([期首金額]+[7月仕入金額])/([期首数量]+[7月仕入数量])*[7月売上数量] AS 7月払出金額, Sum(IIf([月]<=0,[仕入数量]-[売上数量])) AS 7月数量, [期首金額]+Sum(IIf([月]=0,[仕入金額]))-[7月払出金額] AS 7月金額,

～中略～

FROM 
( SELECT 商品ID, 商品名, -1 AS 月, 期首数量 AS 仕入数量, 期首金額 AS 仕入金額, 0 AS 売上数量, 0 AS 売上金額
  FROM t商品マスタ, t決算日
  union all
  SELECT 仕入商品ID, "", Month([仕入日])-7 Mod 12, 仕入数量, 仕入単価*仕入数量, 0, 0
  FROM t仕入明細, t決算日
  WHERE 仕入日 Between DateAdd("yyyy",-1,[決算日]+1) And [決算日]
  union all
  SELECT 売上商品ID, "", Month([売上日])-7 Mod 12, 0, 0, 売上数量, 売上単価*売上数量
  FROM t売上明細, t決算日
  WHERE 売上日 Between DateAdd("yyyy",-1,[決算日]+1) And [決算日]
)  AS q
GROUP BY 商品ID;

ついでに「日付」のままだとごちゃごちゃしてしまうところを、扱いやすいように「月（のようなもの）」に変換しています
期首をマイナス1月の仕入れとみなし、7月から翌6月は0月～11月として計算式が単純になるようにしています

※「月次移動平均」に関係する計算式そのものの是非は見ていません

6
tama 2020/04/02 (木) 14:24:22 fc016@60c58

返信ありがとうございます。

教えていたコードをもとに作成してみましたが、12か月分入力すると
また「クエリが複雑すぎます」と言われてしまいました。

自分で変えた所は、

 Sum(IIf([月]=0,[仕入数量])) AS 7月仕入数量,
 Sum(IIf([月]=0,[仕入金額])) AS 7月仕入金額,
 Sum(IIf([月]=0,[売上数量])) AS 7月売上数量,
 ([期首金額]+[7月仕入金額])/([期首数量]+[7月仕入数量])*[7月売上数量] AS 7月払出金額,
 Sum(IIf([月]<=0,[仕入数量]-[売上数量])) AS 7月数量,
 [期首金額]+Nz(Sum(IIf([月]=0,[仕入金額])),0)-Nz([7月払出金額],0) AS 7月金額,

上記、NZを追加したことと

  union all
  SELECT
 仕入商品ID,
 "",
 iif(Month([仕入日])<7,Month([仕入日])+6,Month([仕入日])-7),
 仕入数量,
 仕入単価*仕入数量,
 0,
 0
  FROM t仕入明細, t決算日
  WHERE 仕入日 Between DateAdd("yyyy",-1,[決算日]+1) And [決算日]

modだと12月以降の数字がマイナスになってしまったので上記のように変えてみました。

自分で直した所で式が複雑になってしまったのでしょうか。

よろしくお願いいたします。

7
hiroton 2020/04/02 (木) 16:17:02 b0bf4@f966d

modだと12月以降の数字がマイナスになってしまった

すみません。計算式を間違えていました。

Month([仕入日])+5 Mod 12

見えてる範囲の（画像の）データを作成し試していますが、NZ追加、月算出方法の変更を掛けてもこちらでは問題なく表示がされます。

12か月分入力すると

ということですが、表示できなくなるのは質問同様半年を超えたあたりということでしょうか？
クエリで表示月を減らすと表示されますか？
新規にACCESSファイルを作り、テストデータを作ってみるとどうですか？

8
名前なし 2020/04/03 (金) 10:16:46 6d87b@60c58

新たにデータベース、テーブルを作成し、レコードを数行入力して下記コードで実行してみましたが
10か月未満では表示できましたが、それを超えると表示できなくなってしまいました。

何か他に原因があるのでしょうか。

SELECT
 q.商品ID,
 Max(q.商品名) AS 商品名,
 Sum(IIf(月=-1,仕入数量)) AS 期首数量,
 Sum(IIf(月=-1,仕入金額)) AS 期首金額,
 Sum(IIf([月]=0,[仕入数量])) AS 7月仕入数量,
 Sum(IIf([月]=0,[仕入金額])) AS 7月仕入金額,
 Sum(IIf([月]=0,[売上数量])) AS 7月売上数量,
 ([期首金額]+[7月仕入金額])/([期首数量]+[7月仕入数量])*[7月売上数量] AS 7月払出金額,
 Sum(IIf([月]<=0,[仕入数量]-[売上数量])) AS 7月数量,
 [期首金額]+Nz(Sum(IIf([月]=0,[仕入金額])),0)-Nz([7月払出金額],0) AS 7月金額,

 Sum(IIf([月]=1,[仕入数量])) AS 8月仕入数量,
 Sum(IIf([月]=1,[仕入金額])) AS 8月仕入金額,
 Sum(IIf([月]=1,[売上数量])) AS 8月売上数量,
 ([7月金額]+[8月仕入金額])/([7月数量]+[8月仕入数量])*[8月売上数量] AS 8月払出金額,
 Sum(IIf([月]<=1,[仕入数量]-[売上数量])) AS 8月数量,
 [7月金額]+Nz(Sum(IIf([月]=1,[仕入金額])),0)-Nz([8月払出金額],0) AS 8月金額,

 Sum(IIf([月]=2,[仕入数量])) AS 9月仕入数量,
 Sum(IIf([月]=2,[仕入金額])) AS 9月仕入金額,
 Sum(IIf([月]=2,[売上数量])) AS 9月売上数量,
 ([8月金額]+[9月仕入金額])/([8月数量]+[9月仕入数量])*[9月売上数量] AS 9月払出金額,
 Sum(IIf([月]<=2,[仕入数量]-[売上数量])) AS 9月数量,
 [8月金額]+Nz(Sum(IIf([月]=2,[仕入金額])),0)-Nz([9月払出金額],0) AS 9月金額,

 Sum(IIf([月]=3,[仕入数量])) AS 10月仕入数量,
 Sum(IIf([月]=3,[仕入金額])) AS 10月仕入金額,
 Sum(IIf([月]=3,[売上数量])) AS 10月売上数量,
 ([9月金額]+[10月仕入金額])/([9月数量]+[10月仕入数量])*[10月売上数量] AS 10月払出金額,
 Sum(IIf([月]<=3,[仕入数量]-[売上数量])) AS 10月数量,
 [9月金額]+Nz(Sum(IIf([月]=3,[仕入金額])),0)-Nz([10月払出金額],0) AS 10月金額,

 Sum(IIf([月]=4,[仕入数量])) AS 11月仕入数量,
 Sum(IIf([月]=4,[仕入金額])) AS 11月仕入金額,
 Sum(IIf([月]=4,[売上数量])) AS 11月売上数量,
 ([10月金額]+[11月仕入金額])/([10月数量]+[11月仕入数量])*[11月売上数量] AS 11月払出金額,
 Sum(IIf([月]<=4,[仕入数量]-[売上数量])) AS 11月数量,
 [10月金額]+Nz(Sum(IIf([月]=4,[仕入金額])),0)-Nz([11月払出金額],0) AS 11月金額,

 Sum(IIf([月]=5,[仕入数量])) AS 12月仕入数量,
 Sum(IIf([月]=5,[仕入金額])) AS 12月仕入金額,
 Sum(IIf([月]=5,[売上数量])) AS 12月売上数量,
 ([11月金額]+[12月仕入金額])/([11月数量]+[12月仕入数量])*[12月売上数量] AS 12月払出金額,
 Sum(IIf([月]<=5,[仕入数量]-[売上数量])) AS 12月数量,
 [11月金額]+Nz(Sum(IIf([月]=5,[仕入金額])),0)-Nz([12月払出金額],0) AS 12月金額,

 Sum(IIf([月]=6,[仕入数量])) AS 1月仕入数量,
 Sum(IIf([月]=6,[仕入金額])) AS 1月仕入金額,
 Sum(IIf([月]=6,[売上数量])) AS 1月売上数量,
 ([12月金額]+[1月仕入金額])/([12月数量]+[1月仕入数量])*[1月売上数量] AS 1月払出金額,
 Sum(IIf([月]<=6,[仕入数量]-[売上数量])) AS 1月数量,
 [12月金額]+Nz(Sum(IIf([月]=6,[仕入金額])),0)-Nz([1月払出金額],0) AS 1月金額,

 Sum(IIf([月]=7,[仕入数量])) AS 2月仕入数量,
 Sum(IIf([月]=7,[仕入金額])) AS 2月仕入金額,
 Sum(IIf([月]=7,[売上数量])) AS 2月売上数量,
 ([1月金額]+[2月仕入金額])/([1月数量]+[2月仕入数量])*[2月売上数量] AS 2月払出金額,
 Sum(IIf([月]<=7,[仕入数量]-[売上数量])) AS 2月数量,
 [1月金額]+Nz(Sum(IIf([月]=7,[仕入金額])),0)-Nz([2月払出金額],0) AS 2月金額,

 Sum(IIf([月]=8,[仕入数量])) AS 3月仕入数量,
 Sum(IIf([月]=8,[仕入金額])) AS 3月仕入金額,
 Sum(IIf([月]=8,[売上数量])) AS 3月売上数量,
 ([2月金額]+[3月仕入金額])/([2月数量]+[3月仕入数量])*[3月売上数量] AS 3月払出金額,
 Sum(IIf([月]<=8,[仕入数量]-[売上数量])) AS 3月数量,
 [2月金額]+Nz(Sum(IIf([月]=8,[仕入金額])),0)-Nz([3月払出金額],0) AS 3月金額,

 Sum(IIf([月]=9,[仕入数量])) AS 4月仕入数量,
 Sum(IIf([月]=9,[仕入金額])) AS 4月仕入金額,
 Sum(IIf([月]=9,[売上数量])) AS 4月売上数量,
 ([3月金額]+[4月仕入金額])/([3月数量]+[4月仕入数量])*[4月売上数量] AS 4月払出金額,
 Sum(IIf([月]<=9,[仕入数量]-[売上数量])) AS 4月数量,
 [3月金額]+Nz(Sum(IIf([月]=9,[仕入金額])),0)-Nz([4月払出金額],0) AS 4月金額,


 Sum(IIf([月]=10,[仕入数量])) AS 5月仕入数量,
 Sum(IIf([月]=10,[仕入金額])) AS 5月仕入金額,
 Sum(IIf([月]=10,[売上数量])) AS 5月売上数量,
 ([4月金額]+[5月仕入金額])/([4月数量]+[5月仕入数量])*[5月売上数量] AS 5月払出金額,
 Sum(IIf([月]<=10,[仕入数量]-[売上数量])) AS 5月数量,
 [4月金額]+Nz(Sum(IIf([月]=10,[仕入金額])),0)-Nz([5月払出金額],0) AS 5月金額,


 Sum(IIf([月]=11,[仕入数量])) AS 6月仕入数量,
 Sum(IIf([月]=11,[仕入金額])) AS 6月仕入金額,
 Sum(IIf([月]=11,[売上数量])) AS 6月売上数量,
 ([5月金額]+[6月仕入金額])/([5月数量]+[6月仕入数量])*[6月売上数量] AS 6月払出金額,
 Sum(IIf([月]<=11,[仕入数量]-[売上数量])) AS 6月数量,
 [5月金額]+Nz(Sum(IIf([月]=11,[仕入金額])),0)-Nz([6月払出金額],0) AS 6月金額

FROM (SELECT
 商品ID,
 商品名,
 -1 AS 月,
 期首数量 AS 仕入数量,
 期首金額 AS 仕入金額,
 0 AS 売上数量,
 0 AS 売上金額
  FROM t商品マスタ, t決算日

  union all
  SELECT
 仕入商品ID,
 "",
 (month([仕入日])+5) mod 12,
 仕入数量,
 仕入単価*仕入数量,
 0,
 0
  FROM t仕入明細, t決算日
  WHERE 仕入日 Between DateAdd("yyyy",-1,[決算日]+1) And [決算日]

  union all
  SELECT
 売上商品ID,
 "",
 (month([売上日])+5) mod 12,
 0,
 0,
 売上数量,
 売上単価*売上数量
  FROM t売上明細, t決算日
  WHERE 売上日 Between DateAdd("yyyy",-1,[決算日]+1) And [決算日]
)  AS q
GROUP BY q.商品ID;
9
hiroton 2020/04/06 (月) 17:37:30 dea5e@f966d

現象を確認しました。
hirotonの上げた構文は8月以降の式が想定と異なっていたためエラーにならなかったようです。

いろいろ試してみましたが、一時テーブルを作成して、「n月金額」部分のみ計算させるような（sumもiifもnzもない）クエリを作っても11か月以上同時に表示しようとするとエラーが発生してしまいました。

フィールドを順次参照していることと、そのフィールドが計算式になっていることが重なって「複雑」となるようですが、明確な条件はわかりませんでした。

いろいろやってみたところ、クエリでの計算を減らすとなんとかなることもあるようでした。例えば標準モジュールに以下のユーザー定義関数を作成し、クエリ上の計算式を置き換えます。

Function 払出金額(前月金額, 前月数量, 当月仕入金額, 当月仕入数量, 当月売上数量)
    払出金額 = (前月金額 + 当月仕入金額) / (前月数量 + 当月仕入数量) * 当月売上数量
End Function

Function 当月金額(前月金額, 前月数量, 当月仕入金額, 当月仕入数量, 当月売上数量)
    当月金額 = 前月金額 + Nz(当月仕入金額, 0) - Nz((前月金額 + 当月仕入金額) / (前月数量 + 当月仕入数量) * 当月売上数量, 0)
End Function

：
 Sum(IIf([月]=1,[仕入数量])) AS 8月仕入数量,
 Sum(IIf([月]=1,[仕入金額])) AS 8月仕入金額,
 Sum(IIf([月]=1,[売上数量])) AS 8月売上数量,
 払出金額([7月金額],[7月数量],[8月仕入金額],[8月仕入数量],[8月売上数量]) AS 8月払出金額,
 Sum(IIf([月]<=1,[仕入数量]-[売上数量])) AS 8月数量,
 当月金額([7月金額],[7月数量],[8月仕入金額],[8月仕入数量],[8月売上数量]) AS 8月金額,
：

ただ、表示までこぎつけても、数件のテストデータだけでも表示までにそれなりの時間がかかっていたので、実用できるかはちょっと疑問です。「複雑すぎ」ると言われるのも納得かなと

金額計算部分がフィールドを繰り返し参照する重い処理になっているので、出力用のテーブルを事前に作成しておいて金額部分以外を出力したのち、VBAで金額部分を計算させるのがいいんじゃないのかなぁと思いました
10
hatena 2020/04/08 (水) 10:55:30 修正

「月次移動平均」というのを知らなかったのと、hirotonさんの回答が付いたので、お任せしてましたが、なかなか苦労してますね。

「月次移動平均」というものをちょっと調べてみましたか、下記の理解であってますでしょうか。

期首日からのある月までの合計金額を合計数量で割ったもの。

つまり、今回は6/30が決算日のようですので、
７月度移動平均は、(期首金額+７月金額計)/(期首数量+７月数量計)
８月度移動平均は、(期首金額+7月～８月金額計)/(期首数量+７月～８月数量計)
9月度移動平均は、(期首金額+7月～9月金額計)/(期首数量+７月～9月数量計)
・・・
ということでよろしいでしょうか。

新たにデータベース、テーブルを作成し、レコードを数行入力して下記コードで実行してみましたが

ということなら、そのデータベースのファイルを送付してもらえますか。
右カラムの一番下に「ファイル送信フォーム」のリンクがありますので、そこから送信してください。
内容を確認して、ここにファイルリンクを置きますので、だれでもダウンロードしてそれをもとに実験できますので、的確な回答が付きやすいと思います。
11
hatena 2020/04/08 (水) 12:35:10 修正

ちょっと考えてみました。

まず、下記のようなテーブルを作成します。

テーブル名　T月

フィールド 名データ型
月ID　　数値型（主キー）
期首日　日付/時刻型
月末　　日付/時刻型

月ID に 1 から 12 までの数値を入力します。

下記の更新クエリを実行します。

UPDATE t月, t決算日
SET
 t月.期首日 = DateSerial(Year([決算日]),Month([決算日])-11,0)+1,
 t月.月末 = DateSerial(Year([決算日]),Month([決算日])-11+[月ID],0);

これでt月は下記のようになります。

月ID期首日月末
12019/07/012019/07/31
22019/07/012019/08/31
32019/07/012019/09/30
42019/07/012019/10/31
52019/07/012019/11/30
62019/07/012019/12/31
72019/07/012020/01/31
82019/07/012020/02/29
92019/07/012020/03/31
102019/07/012020/04/30
112019/07/012020/05/31
122019/07/012020/06/30

下記のようなクエリを作成します。

SELECT
 t商品マスタ.商品ID, t商品マスタ.商品名, t商品マスタ.期首数量, t商品マスタ.期首金額,
 t月.月ID
 Sum(t仕入明細.仕入数量) AS 仕入数量計,
 Sum([仕入数量]*[仕入単価]) AS 仕入金額計
FROM t月, t商品マスタ INNER JOIN t仕入明細 ON t商品マスタ.商品ID = t仕入明細.仕入商品ID
WHERE t仕入明細.仕入日 Between [期首日] And [月末]
GROUP BY
 t商品マスタ.商品ID, t商品マスタ.商品名, t商品マスタ.期首数量, t商品マスタ.期首金額,
 t月.月ID
ORDER BY
 t月.月ID;

これで移動平均を計算できるデータは揃うと思いますがいかがでしょうか。

実際のデータでの確認はしてませんので、見落としがあるかもしれません。
考え方を参考にしてください。下記のような考え方です。

  • 日付計算が複雑なので、必要な日付を先に計算してテーブルに格納しておく。→T月
  • T月 と 他のテーブルは結合しない → T月とデータのすべての組み合わせが出力される。
  • これを、[期首日]から[月末]で抽出する。
  • それを集計する。
12
hatena 2020/04/08 (水) 16:42:06 >> 11

上記で仕入関係のデータは得られます。移動平均もクエリ内で計算できると思います。
同様に、売上関係のデータを出力するクエリを作成します。
２つのクエリを[月ID]で結合すれば月別の移動平均データができると思います。

月別データが縦に並びますが、もし、横に並べないなら、クロス集計クエリで横並びに変換できます。

13
tama 2020/04/14 (火) 16:23:48 3204b@60c58

hirotonさん
返信ありがとうございます。

商品数が数百と仕入売上で数千レコードあるのでやはり難しそうですね。

商品別に仕入数量、仕入金額、売上数量をまとめてVBAで金額部分を計算という方法でやってみようと思います。

エクセルでVBAをちょっと触ったくらいの知識しかないので、時間がかかるかもしれませんがまた相談させてください。

hatenaさん
返信ありがとうございます。

月次移動平均は下記の計算方法になります。

・期首在庫数量
・期首在庫金額
・7月仕入数量
・7月仕入金額
・7月払出数量
・7月払出金額　→　（期首在庫金額+7月仕入金額）/（期首在庫数量+7月仕入数量）×　7月払出数量
・7月在庫数量　→　期首在庫数量+7月仕入数量-7月払出数量
・7月在庫金額　→　期首在庫金額+7月仕入金額-7月払出金額

毎月の在庫金額が前月の在庫金額を元に計算してしまうので複雑になってしまうという事ですね。
14
hatena 2020/04/14 (火) 17:37:16

2020/04/08 の私の回答の方法を一度試してみてください。
集計期間の日付を更新クエリで t月 に生成しますので、それを使って集計するようにします。その為に、式はシンプルになります。２つめのクエリで月次移動平均の計算に必要なデータは揃うと思いますので、それに必要な式を追加すればできると思います。

15
hiroton 2020/04/15 (水) 16:06:05 079da@f966d

VBAを使う方法ってことでつらつらと

まずは下準備から
データ出力用のテーブルを用意します。
T月次移動平均表

フィールド名
商品ID
商品名
期首数量
期首金額
7月仕入数量
7月仕入金額
7月売上数量
7月払出金額
7月数量
7月金額
：

本来出力したかったクエリの全フィールドを持ったテーブルです。データ型は適宜設定してください。
各月の「払出金額」と「金額」は平均の処理が入るから倍精度浮動小数点型になるのかな？

データ処理用のクエリを用意します（２つ）
Q月次移動平均表Clear

DELETE T月次移動平均表.*
FROM T月次移動平均表;

ワークテーブルを使いまわすので処理前にまっさらにするようクエリです。

Q月次移動平均基礎データ作成

INSERT INTO T月次移動平均表
SELECT *
FROM (
	SELECT q.商品ID, Max( q.商品名) AS 商品名, Sum( IIf( 月 = -1, 仕入数量)) AS 期首数量
	, Sum( IIf( 月 = -1, 仕入金額)) AS 期首金額
	, Sum( IIf( 月 = 0, 仕入数量)) AS 7月仕入数量, Sum( IIf( 月 = 0, 仕入金額)) AS 7月仕入金額, Sum( IIf( 月 = 0, 売上数量)) AS 7月売上数量, Sum( IIf( 月<= 0, 仕入数量 - 売上数量)) AS 7月数量
	, Sum( IIf( 月 = 1, 仕入数量)) AS 8月仕入数量, Sum( IIf( 月 = 1, 仕入金額)) AS 8月仕入金額, Sum( IIf( 月 = 1, 売上数量)) AS 8月売上数量, Sum( IIf( 月<= 1, 仕入数量 - 売上数量)) AS 8月数量
	, Sum( IIf( 月 = 2, 仕入数量)) AS 9月仕入数量, Sum( IIf( 月 = 2, 仕入金額)) AS 9月仕入金額, Sum( IIf( 月 = 2, 売上数量)) AS 9月売上数量, Sum( IIf( 月<= 2, 仕入数量 - 売上数量)) AS 9月数量
	, Sum( IIf( 月 = 3, 仕入数量)) AS 10月仕入数量, Sum( IIf( 月 = 3, 仕入金額)) AS 10月仕入金額, Sum( IIf( 月 = 3, 売上数量)) AS 10月売上数量, Sum( IIf( 月<= 3, 仕入数量 - 売上数量)) AS 10月数量
	, Sum( IIf( 月 = 4, 仕入数量)) AS 11月仕入数量, Sum( IIf( 月 = 4, 仕入金額)) AS 11月仕入金額, Sum( IIf( 月 = 4, 売上数量)) AS 11月売上数量, Sum( IIf( 月<= 4, 仕入数量 - 売上数量)) AS 11月数量
	, Sum( IIf( 月 = 5, 仕入数量)) AS 12月仕入数量, Sum( IIf( 月 = 5, 仕入金額)) AS 12月仕入金額, Sum( IIf( 月 = 5, 売上数量)) AS 12月売上数量, Sum( IIf( 月<= 5, 仕入数量 - 売上数量)) AS 12月数量
	, Sum( IIf( 月 = 6, 仕入数量)) AS 1月仕入数量, Sum( IIf( 月 = 6, 仕入金額)) AS 1月仕入金額, Sum( IIf( 月 = 6, 売上数量)) AS 1月売上数量, Sum( IIf( 月<= 6, 仕入数量 - 売上数量)) AS 1月数量
	, Sum( IIf( 月 = 7, 仕入数量)) AS 2月仕入数量, Sum( IIf( 月 = 7, 仕入金額)) AS 2月仕入金額, Sum( IIf( 月 = 7, 売上数量)) AS 2月売上数量, Sum( IIf( 月<= 7, 仕入数量 - 売上数量)) AS 2月数量
	, Sum( IIf( 月 = 8, 仕入数量)) AS 3月仕入数量, Sum( IIf( 月 = 8, 仕入金額)) AS 3月仕入金額, Sum( IIf( 月 = 8, 売上数量)) AS 3月売上数量, Sum( IIf( 月<= 8, 仕入数量 - 売上数量)) AS 3月数量
	, Sum( IIf( 月 = 9, 仕入数量)) AS 4月仕入数量, Sum( IIf( 月 = 9, 仕入金額)) AS 4月仕入金額, Sum( IIf( 月 = 9, 売上数量)) AS 4月売上数量, Sum( IIf( 月<= 9, 仕入数量 - 売上数量)) AS 4月数量
	, Sum( IIf( 月 = 10, 仕入数量)) AS 5月仕入数量, Sum( IIf( 月 = 10, 仕入金額)) AS 5月仕入金額, Sum( IIf( 月 = 10, 売上数量)) AS 5月売上数量, Sum( IIf( 月<= 10, 仕入数量 - 売上数量)) AS 5月数量
	, Sum( IIf( 月 = 11, 仕入数量)) AS 6月仕入数量, Sum( IIf( 月 = 11, 仕入金額)) AS 6月仕入金額, Sum( IIf( 月 = 11, 売上数量)) AS 6月売上数量, Sum( IIf( 月<= 11, 仕入数量 - 売上数量)) AS 6月数量
	FROM (
		SELECT 商品ID, 商品名, -1 AS 月, 期首数量 AS 仕入数量, 期首金額 AS 仕入金額, 0 AS 売上数量, 0 AS 売上金額
		FROM t商品マスタ, t決算日
		union all
		SELECT 仕入商品ID, "", ( month( 仕入日) + 5) mod 12, 仕入数量, 仕入単価 * 仕入数量, 0, 0
		FROM t仕入明細, t決算日
		WHERE 仕入日 Between DateAdd( "yyyy", -1, 決算日 + 1) And 決算日
		union all
		SELECT 売上商品ID, "", ( month( 売上日) + 5) mod 12, 0, 0, 売上数量, 売上単価 * 売上数量 FROM t売上明細, t決算日
		WHERE 売上日 Between DateAdd( "yyyy", -1, 決算日 + 1) And 決算日
	) AS q
	GROUP BY q.商品ID
);

各月の「払出金額」と「（集計）金額」を除いた基礎となるデータを追加クエリでワークテーブルに追加するクエリです。

16
hiroton 2020/04/15 (水) 16:35:47 079da@f966d >> 15

メインのVBAコード

Sub 月次移動平均計算()
    Const c_開始月 = 7

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Dim 月順(11) As String
    Dim i As Long
    Dim 前月数量 As Long
    Dim 前月金額 As Double
    Dim 仕入数量 As Long
    Dim 仕入金額 As Double
    Dim 払出数量 As Long
    Dim 払出金額 As Double
    
    'ループ処理したいので月の並びを配列に確保'
    For i = 0 To 11
        月順(i) = (c_開始月 + i - 1) Mod 12 + 1 & "月"
    Next
    '--(ここまで)--'

    Set db = CurrentDb
    
    '下処理クエリ実行'
    db.Execute "Q月次移動平均表Clear"
    db.Execute "Q月次移動平均基礎データ作成"
    '--(ここまで)--'
    
    Set rs = db.OpenRecordset("T月次移動平均表")
    
    Do Until rs.EOF
        rs.Edit
        
        '移動平均計算'
        前月数量 = Nz(rs!期首数量, 0)
        前月金額 = Nz(rs!期首金額, 0)
        For i = 0 To 11
            仕入金額 = Nz(rs(月順(i) & "仕入金額"), 0)
            仕入数量 = Nz(rs(月順(i) & "仕入数量"), 0)
            払出数量 = Nz(rs(月順(i) & "売上数量"), 0)
            
            If 前月数量 + 仕入数量 = 0 Then
                払出金額 = 0
            Else
                払出金額 = (前月金額 + 仕入金額) / (前月数量 + 仕入数量) * 払出数量
            End If
            前月金額 = 前月数量 + 仕入金額 - 払出金額
            
            rs(月順(i) & "払出金額") = 払出金額
            rs(月順(i) & "金額") = 前月金額
            
            前月数量 = Nz(rs(月順(i) & "数量"), 0)
        Next
        '--(ここまで)--'
        
        rs.Update
        rs.MoveNext
    Loop
    
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

変数の型（平均計算で小数点以下がでる）のと/ (前月数量 + 仕入数量)があるのでそこの条件判断をしてあげないといけないかなぁという感じ
計算部分はちょっと自信ないので出力結果が正しいかは十分にテストしてください

テーブル、クエリ、VBAと使うものが多いのでフィールド名を変えたりする場合はよく見てください

ワークテーブルを使う方法は実行する度にACCESSファイルが肥大化するので適宜、最適化が必要になると思います。大本のデータとはファイルを分けてリンクテーブルで拾うようにするといいかな？

いろいろ手間な部分もあるのでhatenaさんの方法でうまくいけばそれでいいんじゃないかな。とも

17
POI53 2024/06/07 (金) 22:01:30 c6f3c@ef621

すでに解決済みかもしれませんが、最近この症状にはまって一応解決したのでコメントさせて頂きます
解決方法は、Accessを64bitで再インストールしたらエラーがなくなりました、根本的な解決ではないかもしれません
私の場合32bitだったのでもしやと思い64bitにしたというだけです、何らかの上限や制約があるかもしれないのでもしかするともっとクエリを複雑にすると64bitでも同じ症状が出るかもしれません
何かの参考になれば幸いです

18
りんご 2024/06/07 (金) 23:41:13 935bc@0e907

原因がはっきりしているエラーなので、根本的に設計からやり直して解決するしかないでしょう。臭い物に蓋をしても遅かれ早かれ損切りするしかないですわー。