Microsoft Access 掲示板

Microsoft Access 掲示板

クエリが複雑すぎます / 15

18 コメント
views
4 フォロー
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さんの方法でうまくいけばそれでいいんじゃないかな。とも