会社コード、品目コード、月度、出荷数の入った明細テーブルがあります
これを過去3ヶ月で出荷数を移動平均したいのです
例 元データ
会社コード、品目コード、月度、出荷数
A00000001,AAAAAAA,201410,10
A00000001,AAAAAAA,201410,10
A00000001,AAAAAAA,201408,10
B00000002,BBBBBBB,201408,10
出力したい値
会社コード、品目コード、月度、出荷数
A00000001,AAAAAAA,201410,10←出荷数は同じ会社コード、品目コードの(10月+9月+8月)÷3)
A00000001,AAAAAAA,201409,3.33←出荷数は同じ会社コード、品目コードの(9月+8月+7月)÷3)
A00000001,AAAAAAA,201408,3.33←出荷数は同じ会社コード、品目コードの(8月+7月+6月)÷3)
B00000002,BBBBBBB,201408,3.33←出荷数は同じ会社コード、品目コードの(8月+7月+6月)÷3)
このような事がしたいのですがどのようにしたら実現できるでしょうか
月度で移動平均なんて良く使いそうな気がしますが・・簡単なようで難しく悩んでおります
どうかよろしくお願い致します
No.15ベストアンサー
- 回答日時:
>その計算の元ネタとなる過去3ヶ月間の移動平均出荷数が求めたかったのです。
私の最終回答で、出荷数=0の月も含めて求められていると思いますよ。
7月---10
8月---10
9月---0 ----->(10+10+0)/3・・・(7月+8月+9月)/3
10月--10 ---->(10+0+10)/3・・・(8月+9月+10月)/3
11月--10 ---->(0+10+10)/3・・・(9月+10月+11月)/3
添付図では、このように計算しています。こういう計算結果を9月にも出力するには、9月にも出荷数=0の行も必要。その為に、ユニオンクエリで求めた出荷数=0を全ての月に加えています。
1、ユニオンクエリーで集荷数=0を全ての組み合わせを全ての月に追加してもDistinct句を使えば出力では重複することはありません。
2、ユニオンクエリーで集荷数=0を全ての組み合わせを全ての月に追加しても、移動平均出荷数の計算結果は同じです。10+0=10ですから。
添付図の結果は、[会社コード], [品目コード], [月度],[集荷数]=0を追加した後に、次のSQL文を実行したもの。
SELECT DISTINCT [会社コード], [品目コード], [月度], Format(Rounds(DSum("出荷数", "出荷履歴", WHERE 会社コード='" & [会社コード] & "' AND 品目コード='" & [品目コード] & "' AND 月度 BETWEEN ' MoveMonth([月度],-3) & "' AND '" & [月度] & "'")/3,0,2),"0.00") AS 平均出荷数
FROM 出荷履歴
WHERE 月度 BETWEEN "201408" AND "201410"
ORDER BY [会社コード], [品目コード], [月度];
動かないとすれば、単なる転記ミスです。
1、DISTINCT句を使っていること。
これで、ダミー行を出力から追放することができます。
2、表示する期間を指定していること。
表示する期間は、まあ、実際に運用する際に全期間の結果を求めないだろうと考えたからに過ぎません。
なお、MoveMonth([月度],-3) でBETWENNを簡略化、あるいはRounds()で四捨五入していること。これらは、枝葉の問題。
要は、[会社コード], [品目コード], [月度],[集荷数]=0を追加すればよいだけと思いますが・・・。
ありがとうございます
>>1、ユニオンクエリーで集荷数=0を全ての組み合わせを全ての月に追加してもDistinct句を使えば出力では重複することはありません
この考え方は目からうろこです
そうですね、足りない月のレコードだけを追加する事にこだわって難しく考えていたかもしれません
全部の月に対して出荷数0のレコードを追加した後で
select 会社コード、品目コード、sum(出荷数) from hoge group by 会社コード、品目コード
グルーピングしてあげればもともとレコードのない月は出荷数0のレコードとして出力されますね
No.17
- 回答日時:
【疑問】今のアプローチは正しいのか?
>毎月の在庫回転期間を求めたいのです。
商品名|月次出庫数|月次期首在庫|月次期末在庫|月次在庫回転率|月次在庫回転期間
AA1|__100|___100|_____0|__2.000|___0.5ヶ月
AA2|___50|___100|____50|__0.666|___1.5ヶ月
AA3|____0|___100|___100|__0.000|________
商品AA1は、月次平均在庫数は50。
これを一ヶ月で2倍の100個出庫。
ですから、在庫回転期間は0.5ヶ月。
商品AA2は、月次平均在庫数は75。
これを一ヶ月で50個出庫。
ですから、在庫を出庫しきるにはもう半月を必要とします。
よって、在庫回転期間は1.5ヶ月。
敢えて、単月で「在庫回転期間]を出すとすれば上図のようです。
この結果を、更に、次のようにレポートしたい。
商品名|7月度在庫回転期間|8月度在庫回転期間|9月度在庫回転期間|平均在庫回転期間|
AA1|____1/2ヶ月|____1/2ヶ月|____1/2ヶ月|___1/2ヶ月|
ならば、
課題は、単月での「在庫回転期間]を求めるだけで事足りるのではと思いますよ。「商品別在庫回転期間推移表」は、単に、レポート出力の手の問題。
【在庫回転期間の定義は横に置いて】
要は、「商品別在庫回転期間推移表」をレポートすれば事足りる訳ですので・・・
<<テーブル「在庫回転台帳」>>
ID=1
会社コード=XXXX
商品コード=XXXX
<<テーブル「在庫回転明細」>>
ID=1
在庫回転台帳_ID=1
月次=201405
月次月次出庫数=100
月次期首在庫=100
月次期末在庫=0
月次在庫回転率=2.00
※列[月次在庫回転期間]は、月次在庫回転率の逆数なので必要ありません。
※もちろん、質問者のいう「在庫回転率」でも同じ設計ですよ。
ID=1
在庫回転台帳_ID=1
月次=201405
前々月出庫数=XXX
前月出庫数=XXX
当月出庫数=XXX
月次在庫回転期間=XXX
↑
あくまでも雛形。私には、月次在庫回転期間の求め方がよく理解できていませんので当てずっぽうで書いています。
という二つのテーブルを生成すれば簡単に目的は達成できるかと思いますよ。「商品別在庫回転期間推移表」の出力は小一時間で終了します。ですから、レポート出力のことは一切考える必要はありません。
No.16
- 回答日時:
>要は、[会社コード], [品目コード], [月度],[集荷数]=0を追加すればよいだけと思いますが・・・。
もちろん、追加するユニオンは集計期間によって適宜作成するひつようがあります。
<例>
201406・・・201404,201405に登場する全てetc.
201407・・・201405,201406に登場する全てetc.
201408・・・201406,201407に登場する全てetc.
201409・・・201407,201408に登場する全てetc.
201410・・・201409,201410に登場する全てetc.
なお、
201404に登場したのは201405,201406にも発生させる
201405に登場したのは201406,201407にも発生させる
201406に登場したのは201407,201408にも発生させる
という考えもあります。
一体、どれが最も欲するものか?それは、実践をもって確認して下さい。それによっては、独自のユニオンになるかも知れません。要は、そのアイデアにのみ着目してもらえれば幸いです。
PS, MoveMonyh(XXXX,-3)→MoveMonyh(XXXX,-2)
No.14
- 回答日時:
INSERT INTO 出荷履歴
SELECT *
FROM クエリ19;
これをやりたい意味が判りました。やっぱり移動平均を出したかったということでしょう。
思うに、やられていることはExcel的な処理にAccessを利用しているだけ。データも[出荷履歴]というより[月次出荷合計明細票]の類。だったら、INSERT文で解決も手です。ダミー行を追加しても用が済んだら削除すれば済む話。
ありがとうございます
説明不足な点があり混乱させてしまったようです
やりたかった事はNO8さんのところに書いたのですが
在庫回転期間を求めたかったのです
その計算の元ネタとなる過去3ヶ月間の移動平均出荷数が求めたかったのです
入手できるデータは他に月末在庫データ
会社コード、品目コード、月度、数量の項目です
この月末在庫データと出荷履歴データを使って各月の在庫回転期間をNO8さんのところに書いた
計算式で求めたかったのが最終的な目的です
最初にこれを説明していればもっと簡単な方法があったかもしれないです
ごめんなさい
No.13
- 回答日時:
データがない月度も表示させたいということですね。
表示したい月度の格納されたマスターテーブルを作成して、それと明細テーブルをクエリで外部結合すれば表示させることができます。
会社マスター、品目マスターテーブルも必要になりますが、ありますよね。
具体的な方法の提示は、現状のマスターテーブル等の名前、フィールド構成等が不明なのでできませんので、
とりあえずやってみて、できなかったら、上記の詳細の情報を追加して、どこまでできて、どこで躓いたのか提示して、返信してください。
No.12
- 回答日時:
【補足の補足】移動平均法と出荷日
A商品の出荷は6、7月のみ。
B商品の出荷は7、8月のみ。
C商品の出荷は8、9月のみ。
D商品の出荷は10月のみ。
現実には、こういう状況が一般的。だとすれば、各々の商品の3ヶ月の移動平均を出力しても実に評価が難しいデータになるかと思いますよ。回答1で問うたのは、
>本当に移動平均ですか?
>単なる過去3ヶ月間の平均ですか?
このこと。ちょーっと、最初から喉にトゲがひかかったままなので補足して確認しておきます。
No.11
- 回答日時:
【疑問】移動平均法と出荷日との関係
提示されているデータの出荷日は月で管理されています。つまり、過去3ヶ月を正確に指定できません。11月01日の伝票があった場合には09/01~11/01の約2ヶ月の平均出荷数になります。先の回答で、
>11月初旬の処理に問題があって・・・
と書いていますが、その意味を補足しておきます。出荷日が日付で管理されていればと思っています。
No.10
- 回答日時:
できるというだけで、使えるのかわかりませんけど
テーブル名を「★★」と仮定します。
連続した月度生成用のテーブル「T月度」を新設します。
フィールドは「月度」(長整数)のみ
標準モジュールに以下を記述しておきます。
やっている内容は、テーブル「★★」内の最小月度、最大月度を求めて
その範囲の連続した yyyymm を作成します
Public Function myInit() As Boolean
Dim rs As New ADODB.Recordset
Dim sSql As String
Dim dtS As Date, dtE As Date
sSql = "DELETE * FROM T月度;"
CurrentProject.Connection.Execute sSql
rs.Source = "SELECT Min(月度), Max(月度) FROM ★★;"
rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If (Not rs.EOF) Then
dtS = CDate(Format(rs(0), "0000/00"))
dtE = CDate(Format(rs(1), "0000/00"))
rs.Close
rs.Open "T月度", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
While (dtS <= dtE)
rs.AddNew
rs(0) = Int(Format(dtS, "yyyymm"))
rs.Update
dtS = DateAdd("m", 1, dtS)
Wend
rs.Close
End If
myInit = True
End Function
この関数をSQLから呼び出して利用します。
以下の内容をSQLビューに記述した場合と結果は
SELECT Q1.会社コード, Q1.品目コード, Q1.月度, Round(Sum(Q2.出荷数)/3,2) AS 平均 FROM
(SELECT * FROM
(SELECT DISTINCT 会社コード, 品目コード FROM ★★), T月度) AS Q1
, ★★ AS Q2
WHERE myInit() AND
Q1.会社コード=Q2.会社コード AND Q1.品目コード=Q2.品目コード AND
Q2.月度 IN (SELECT TOP 3 月度 FROM T月度 WHERE 月度<=Q1.月度 ORDER BY 月度 DESC)
GROUP BY Q1.会社コード, Q1.品目コード, Q1.月度;
会社コード 品目コード 月度 平均
A00000001 AAAAAAA 201408 3.33
A00000001 AAAAAAA 201409 3.33
A00000001 AAAAAAA 201410 10
B00000002 BBBBBBB 201408 3.33
B00000002 BBBBBBB 201409 3.33
B00000002 BBBBBBB 201410 3.33
B00000002 BBBBBBB の 201409 / 201410 は要らないのなら
SELECT Q1.会社コード, Q1.品目コード, Q1.月度, Round(Sum(Q2.出荷数)/3,2) AS 平均 FROM
(SELECT * FROM
(SELECT DISTINCT 会社コード, 品目コード FROM ★★) AS T1, T月度 AS T2
WHERE T2.月度<=(SELECT Max(月度) FROM ★★ WHERE 会社コード=T1.会社コード AND 品目コード=T1.品目コード)) AS Q1
, ★★ AS Q2
WHERE myInit() AND
Q1.会社コード=Q2.会社コード AND Q1.品目コード=Q2.品目コード AND
Q2.月度 IN (SELECT TOP 3 月度 FROM T月度 WHERE 月度<=Q1.月度 ORDER BY 月度 DESC)
GROUP BY Q1.会社コード, Q1.品目コード, Q1.月度;
会社コード 品目コード 月度 平均
A00000001 AAAAAAA 201408 3.33
A00000001 AAAAAAA 201409 3.33
A00000001 AAAAAAA 201410 10
B00000002 BBBBBBB 201408 3.33
※ サンプルでできたというだけですので・・・
No.9
- 回答日時:
1、[会社コード]+[品目コード]+[月度]
の組合せで全ての月に出荷数=0を求めるクエリを作成。
2、INSERT INTO XXX SELECT XXX 同クエリ
を実行する。
以上で確かに現象的には目的を達成出来ますが・・・。これは、ちょっと非現実的かと思いますね。テーブル[出荷履歴]には、主キーもあるでしょう。また、他のテーブルとのリンクも張ってあるでしょう。そこら辺りに十分に配慮してとなると、INSERT INTO 出荷履歴 SELECT * FROM クエリ19;と単純にいけるものか?VBAを推す理由です。
【出荷数=0を発生させるクエリ】
SELECT DISTINCT
[会社コード],
[品目コード],
"201408" AS [月度],
0 AS [出荷数]
FROM 出荷履歴 WHERE 月度 BETWEEN "201408" AND "201410"
UNION
SELECT DISTINCT
[会社コード],
[品目コード],
"201409" AS [月度],
0 AS [出荷数]
FROM 出荷履歴 WHERE 月度 BETWEEN "201408" AND "201410"
UNION
SELECT DISTINCT
[会社コード],
[品目コード],
"201410" AS [月度],
0 AS [出荷数]
FROM 出荷履歴 WHERE 月度 BETWEEN "201408" AND "201410";
【INSERT文】
INSERT INTO 出荷履歴
SELECT *
FROM クエリ19;
No.8
- 回答日時:
データのない月度も出力したいということですね。
回答の前にもう少し確認させてください。
> 追加したいデータは
>
> A00000001,AAAAAAA,201409,0
> B00000002,BBBBBBB,201410,0
> B00000002,BBBBBBB,201409,0
A00000001,BBBBBB,201410,0
A00000001,BBBBBB,201409,0
A00000001,BBBBBB,201408,0
は出力しなくていいのですか。
会社A00000001の取り扱う品目はAAAAAAだけなんでしょうか。
同様に、B00000002 の場合はAAAAAAは出力しないのですか。
会社のマスターテーブルは存在しますか。
また、それぞれの会社の取り扱い品目の情報はどのように管理してますか。
> これを一気に追加することが追加クエリでできるでしょうか?
追加クエリでデータを追加しなくても、マスターテーブルがあれば外部結合を使うことで実現はできます。
ただ、具体的な方法は、上記のマスターテーブルや取り扱い品目のテーブルの構成がどうなっいてるかが判明しないと提示できないですね。
コメントありがとうございます会社のマスターテーブルは存在しますが
自分はそのデータを入手する権限がありません
説明不足で申し訳ありません
何がしたいかというと毎月の在庫回転期間を求めたいのです
月末在庫のデータと出荷履歴のデータは入手できます
10月末の在庫数÷(10月の出荷数+9月の出荷数+8月の出荷数)÷3=10月の在庫回転期間
9月末の在庫数÷(9月の出荷数+8月の出荷数+7月の出荷数)÷3=9月の在庫回転期間
8月末の在庫数÷(8月の出荷数+7月の出荷数+6月の出荷数)÷3=8月の在庫回転期間
上記の値を会社コード、品目コード別に出力したかったのです
なので
月末在庫は存在しても出荷した事が一度もない不良在庫の物もありますので
9月に出荷した履歴がなくても(9月の出荷数+8月の出荷数+7月の出荷数)÷3で
3ヶ月の平均出荷数は求められると思います
なので出荷した履歴がない月も出荷数の平均を求めたかったのです
Vlookupで在庫と出荷平均を後で結合したデータを作って計算したかったので
出荷0の月もデータを出したいという事です
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 【マクロ】ファイル名の日付によって、保管するフォルダを、自動選択したい 4 2023/08/16 11:24
- Amazon クロネコヤマトの集荷を依頼した場合ネコピットは利用できますか? 1 2023/05/21 19:02
- 会社・職場 ある時、 会社で見積書の依頼を メールで対応してる時の出来事。 見積書な項目8行目の文言が 長すぎる 2 2023/06/10 03:12
- Excel(エクセル) マクロでPDFを印刷する方法 3 2023/02/15 09:40
- Visual Basic(VBA) 前回ご教授いただいたコードに覚えたてのループ処理で品名りんごAから順に20回for nextでループ 7 2023/01/13 22:01
- Visual Basic(VBA) VBAで質問があります 1 2022/10/19 10:32
- 作詞・作曲 或る曲の楽譜のコード進行の中に"F onA"なるものがありました。これはどんな和音ですか? 7 2023/05/22 00:34
- アジア 今月、成田からマレーシアへANAで旅行予定です。 荷物に付いてよくわからないので教えて下さい。 お預 1 2022/07/12 19:11
- その他(暮らし・生活・行事) 引っ越し荷物の一時預かり 2 2022/07/04 23:24
- その他(Microsoft Office) EXCEL VLOOKUPに関する質問 5 2023/02/08 11:38
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
「マスタ」と「テーブル」の違...
-
PLSQLの識別子エラー
-
ACCESS 一つのフィールドに複...
-
2つのテーブルから条件に一致...
-
主キーの変更
-
SELECT時にカウントアップ
-
行方向のデータを横に並べる
-
商品コード番号を入力すると商...
-
select文の書き方
-
filemakerでのポータルの設定の...
-
Accessでフィールドを比較した...
-
VIEWでテーブルの集計結果...
-
Accessリレーションシップ
-
ACCESSのSQLで、NULLかNULLでな...
-
Accessにインポートしたら並び...
-
ACCESS2007 フォーム 「バリア...
-
主キーにインデックスは貼らな...
-
ACCESSのBookmarkプロパティの...
-
Accessでクエリを完了できませ...
-
MongoDBのデータ更新はDBを排他...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
「マスタ」と「テーブル」の違...
-
2つのテーブルから条件に一致...
-
重複するキーから一番古い年月...
-
ACCESS 一つのフィールドに複...
-
行方向のデータを横に並べる
-
PLSQLの識別子エラー
-
VIEWでテーブルの集計結果...
-
Accessでフィールドを比較した...
-
Accessユニオンクエリーで2つ...
-
主キーの変更
-
ACCESSのコンパイルエラーについて
-
片方だけ抽出する方法(SQL)
-
【Access】順位を付けたい
-
[Oracle] UPDATE分の副問い合わ...
-
テーブル値関数経由でのデータ更新
-
ACCESS インポート時の重複チ...
-
ACCESS2000でDCount関数の使い方
-
Access2002の集計レポート?
-
商品コード番号を入力すると商...
-
Inner join と Left joinの明...
おすすめ情報