accessで移動平均する方法

会社コード、品目コード、月度、出荷数の入った明細テーブルがあります
これを過去３ヶ月で出荷数を移動平均したいのです

例 元データ
会社コード、品目コード、月度、出荷数
A00000001,AAAAAAA,201410,10
A00000001,AAAAAAA,201410,10
A00000001,AAAAAAA,201408,10
B00000002,BBBBBBB,201408,10

出力したい値
会社コード、品目コード、月度、出荷数
A00000001,AAAAAAA,201410,10←出荷数は同じ会社コード、品目コードの（１０月＋９月＋８月）÷３）
A00000001,AAAAAAA,201409,3.33←出荷数は同じ会社コード、品目コードの（９月＋８月＋７月）÷３）
A00000001,AAAAAAA,201408,3.33←出荷数は同じ会社コード、品目コードの（８月＋７月＋６月）÷３）
B00000002,BBBBBBB,201408,3.33←出荷数は同じ会社コード、品目コードの（８月＋７月＋６月）÷３）

このような事がしたいのですがどのようにしたら実現できるでしょうか
月度で移動平均なんて良く使いそうな気がしますが・・簡単なようで難しく悩んでおります
どうかよろしくお願い致します

No.7 回答者： f_a_007 回答日時： 2014/10/23 23:36

【お詫びと補足】

補足質問の意味をやっと理解できました。つまり。添付図のような出力で対象月を全て出力したいということですね。私なら、VBAで期間を設定して出荷数=0の月があればInsert文を実行します。

理由：SQL文を考えるのが面倒臭いから！

[会社コード+品目コード+月次]で対象月に発生しているの全てに追加しないと目的は達成できないでしょう。ということは、添付図のクエリの結果を配列に取り込んで欠落月を挿入するというやり方が一番最初に思い付きます。ですから、それをVBAにて記述します。

No.6 回答者： f_a_007 回答日時： 2014/10/23 22:39

訂正：

×　８～9月
〇　8～10月

No.5 回答者： f_a_007 回答日時： 2014/10/23 22:37

＞月次の出荷数が0の場合・・・

なぜ、追加する必要があるのですか？
無くても集計はする筈ですが・・・？
添付図のような結果ではダメってことですか？

No.4 回答者： hatena1989 回答日時： 2014/10/23 20:29

移動平均にもいろいろありますが、

移動平均 - Wikipedia
http://ja.wikipedia.org/wiki/%E7%A7%BB%E5%8B%95% …

今回の事例は、上記の「単純移動平均」と私は解釈しました。

質問のデータ例が間違っているようですが、
やりたいことは下記のようなことですよね。

元データ
A00000001,AAAAAAA,201410,10
A00000001,AAAAAAA,201409,10
A00000001,AAAAAAA,201408,10
B00000002,BBBBBBB,201408,10

出力
A00000001,AAAAAAA,201410,10
A00000001,AAAAAAA,201409,6.66
A00000001,AAAAAAA,201408,3.33
B00000002,BBBBBBB,201408,3.33


月度 201409 の移動平均は、（９月＋８月＋７月）÷３ ですが、7月がないので２０÷３=6.66・・・
になりますね。

ちなみに私の最初の回答の式にタイプミスがありました。
「Brtween」を「Between」に修正してください。

SQLも出しておきますね。

SELECT 会社コード, 品目コード, 月度,
DSum("出荷数","元データ","会社コード='" & [会社コード] & "' AND 品目コード='" & [品目コード] & "' AND 月度 Between " & Format(DateAdd("m",-3,Format([月度],"0000\/00")),"yyyymm") & " AND " & [月度])/3 AS 平均出荷数
FROM 元データ;

なお、丸め（四捨五入、切り捨て等）については、特に指定がないので考慮してません。
指定があるなら追記してください。

この回答へのお礼

ありがとうございます
説明不足だった事に気がつきました

確かに教えて頂いた方法で計算できたのですが
問題は出荷が０件の時の扱いを説明していませんでした
最初の私の例では201409のデータは書いていませんが
これは出荷が０だったので明細データとして存在しないという意味です
そしてデータを収集を開始したのが８月から１０月までなので
７月以前の移動平均の結果は出力する必要はないのですが
出荷数０の月の移動平均の出力結果も出したいのです

元データに８月～１０月で存在しない月のデータがあったら出荷数０でデータを追加した後に
クエリを動かせば目的のものができるのですが
データ量が多いのでこの出荷数０を補完するデータをどのように作り出せばいいか今悩んでいます
追加したいデータは

A00000001,AAAAAAA,201409,0
B00000002,BBBBBBB,201410,0
B00000002,BBBBBBB,201409,0

になります
これを一気に追加することが追加クエリでできるでしょうか？

お礼日時：2014/10/23 20:46

No.3 回答者： f_a_007 回答日時： 2014/10/23 17:37

どうやら、私の解釈は合っているようなのでSQL文全体を補足します。

SELECT
　　DISTINCT [会社コード], [品目コード],
　　Rounds(Nz(DSum(・・・・・))/3,1) AS 平均出荷数
FROM 出荷履歴
WHERE 月度 BETWEEN "201408" AND "201410";

※DSum()関数内の書き方は、既に回答が付いているので割愛します。

直近3ヶ月ということですが、11月初旬ですと8月～10月ということも考えられます。そこら辺りをどのように考えておられるのかが不明。よって、一応、回答では開始月と終了月とを埋め込んでいます。実際に運用される場合は、しかるべく修正されて下さい。

注意点１、DSum()は Null 値を戻すこともあります。
　　　　　Nz(DSum()) と Null を 0に変換しておいたが無難です。
注意点２、Select文も期間を指定する必要があります。
　　　　　でないと、指定期間以外も出力します。
注意点３、Distinct句を指定した方がいいです。
　　　　　[会社コード], [品目コード]の組合せの重複出力を回避できます。

以上、補足しておきます

この回答へのお礼

ありがとうございます
出荷数０の時の扱いの説明が不足していた事に気がつきました
詳しくはＮｏ４さんのところに書いています
やりたい事の半分はできたのですが・・・

お礼日時：2014/10/23 20:48

No.2 回答者： hatena1989 回答日時： 2014/10/23 08:22

元データテーブルからクエリを作成して、フィールド欄に下記の式を設定すればいいでしょう。

平均出荷数: DSum("出荷数","元データ","会社コード='" & [会社コード] & "' AND 品目コード='" & [品目コード] & "' AND 月度 Brtween " & Format(DateAdd("m",-3,Format([月度],"0000\/00")),"yyyymm") & " AND " & [月度])/3

この回答へのお礼

ありがとうございます
出荷数０の時の扱いの説明が不足していた事に気がつきました
詳しくはＮｏ４さんのところに書いています
やりたい事の半分はできたのですが・・・

お礼日時：2014/10/23 20:48

No.1 回答者： f_a_007 回答日時： 2014/10/22 22:43

【出荷履歴】

1_|A00000001|AAAAAAA|201408|10
2_|A00000001|AAAAAAA|201409|20
3_|A00000001|AAAAAAA|201410|30
4_|B00000002|BBBBBBB|201408|10
5_|B00000002|BBBBBBB|201409|20
6_|B00000002|BBBBBBB|201410|30
7_|C00000003|CCCCCCC|201408|10
8_|A00000001|AAAAAAA|201411|10

【Step1】

SELECT DISTINCT 会社コード, 品目コード
FROM 出荷履歴;

＜結果＞

A00000001|AAAAAAA
B00000002|BBBBBBB
C00000003|CCCCCCC

さて、このSQL文に

同じ会社コードと商品コードを持つ行の出荷数の合計をBETWEENで期間を指定して3で除して四捨五入すれば、添付図のような結果を得ることができると思いますよ。少し課題があるとすれば、AccessのRound()は銀行型丸めで四捨五入ではないことです。ですから、仮に四捨五入したい場合は、以下のような Rounds() を標準モジュールに付加する必要があります。

Public Function Rounds(ByVal M As Currency, _
　　　　　　　　　　　 ByVal A As Integer, _
　　　　　　　　　　　 Optional D As Integer = 0) As Variant
　　Dim R As Currency
　　
　　Select Case A
　　　　Case 0 ' 四捨五入
　　　　　　R = Fix(M * 10 ^ D + 0.5@)
　　　　Case 1 '切り捨て
　　　　　　R = Fix(M * 10 ^ D)
　　　　Case 2 ' 切り上げ
　　　　　　If Int(M) <> M Then
　　　　　　　　R = Fix(M * 10 ^ D) + 1 * 10 ^ D
　　　　　　Else
　　　　　　　　R = Fix(M * 10 ^ D)
　　　　　　End If
　　End Select
　　Rounds = Sgn(M) * (R / 10 ^ D)
End Function

なお、私の解釈で合っていれば添付図のSQL文を補足します。ただ、

月度 BETWEEN '201408' AND '201410'

と、集計月度の期間データはSQLに埋め込んでいます。

この回答へのお礼

ありがとうございます
出荷数０の時の扱いの説明が不足していた事に気がつきました
詳しくはＮｏ４さんのところに書いています
やりたい事の半分はできたのですが・・・

お礼日時：2014/10/23 20:49