Access 直近3か月及び6か月のデータ集計

質問

早速ですが、皆様のお知恵を拝借したく投稿します。

販売記録をテーブルに入力している状態で（下記参照）、直近6か月の販売金額の合計と直近3か月の販売金額の合計を同じ表の中で表現したく思っています。

販売記録テーブル
|販売月|品名|個数|単価|売上|
|2014/6|AAA|　　2|　100|　200|
|2014/8|BBB|　　1|　200|　200|
|2014/9|CCC|　　1|　200|　600|

上記データであれば以下のように表現させたいのです。

|直近3か月|直近6か月|
|　　　　 800|　　　　1000|

このようなことは可能でしょうか？
できるとすればどのような方法でできますでしょうか？

よろしくご教示いただけますと幸いです。

回答No.1

山田 太郎（@f_a_007） · ベストアンサー

これは、ちょっと苦労するでしょうね。問題は、MoveMonth()ですね。

Public Function MoveMonth(ByVal strMonth As String, _
　　　　　　　　　　　　　ByVal intMove As Integer, _
　　　　　　　　　　　　　Optional strFormat As String = "yyyy/mm") As String
　　Dim YYYY　 As Long
　　Dim MM　　 As Long
　　Dim YYYYMM As String

　　YYYYMM = Format(strMonth, "yyyymm")
　　YYYY = Val(YYYYMM) / 100
　　MM = Val(YYYYMM) - YYYY * 100
　　MoveMonth = Format(DateSerial(YYYY, MM + intMove, 1), strFormat)
End Function

イミディエイトウインドウで確認してもらえればわかりますが、これで WHERE 節が書けるようになります。

SELECT
　　DSum("個数*単価","販売履歴",
　　　　　"販売月 Between MoveMonth('2014/9', -2, 'yyyy/m') AND '2014/9'") AS 直近3か月,
　　DSum("個数*単価","販売履歴",
　　　　　"販売月 Between MoveMonth('2014/9', -5, 'yyyy/m') AND '2014/9'") AS 直近6か月;

SQL文は、このように書けば目的を達成できます。

PS、"2014/09"の埋め込みについて

あくまでもサンプルということ。現実には、VBAなどで基準月を指示することになろうかと思います。

お礼

当方がまだAccessのVBAには明るくなく、おそらくコード丸写しになってしまうかもしれないという懸念はありますが、丁寧にサンプルまで作って教えて頂いてありがとうございました！
現状急ぎの作業ではないので、フォーマットの指定も考慮しつつお教えいただいた手法でやってみます！

回答No.5

山田 太郎（@f_a_007）

【補足】日付型として・・・

添付図のように日付型であれば、YYYY/M　も YYYY/MM も同じ。

SELECT
DSum("個数*単価",
　　 "販売履歴",
　　 "CDATE(販売月) Between #" & MoveMonth('2014/9',-2,'yyyy/m') & "# AND #2014/9#")
　　 AS 直近3か月,
DSum("個数*単価",
　　 "販売履歴",
　　 "CDATE(販売月) Between　#" & MoveMonth('2014/9',-5,'yyyy/m') & "# AND #2014/9#")
AS 直近6か月;

あくまでも YYYY/M　に拘るのであれば、[販売月]は日付型として扱うことをお勧めします。

お礼

当方がまだAccessのVBAには明るくなく、おそらくコード丸写しになってしまうかもしれないという懸念はありますが、丁寧にサンプルまで作って教えて頂いてありがとうございました！
現状急ぎの作業ではないので、フォーマットの指定も考慮しつつお教えいただいた手法でやってみます！

回答No.4

山田 太郎（@f_a_007）

＞yyyy/m では
＞'2014/10' < '2014/5'
＞ですから、期間が正しく判定できません。

これは、その通りですね。
まあ、多分、そうだろうと思って・・・。

Between MoveMonth('2014/9', -2, 'yyyy/m') AND '2014/9'")
　↓
Between MoveMonth('2014/9', -2) AND '2014/09'")

とすれば、この不具合は回避できます。その為に、MoveMonth()関数は、"yyyy/m"、"yyyym"形式で受け取ろうともデフォルトで"yyyy/mm"形式で返すように仕組んでいます。

回答No.3

m3_maki

[販売月] の データ型 や 実際のデータの内容によって変わってきます。

テキスト型 で、サンプルのような記述であれば 下記のクエリで。
データ量が多ければ
Where 条件 を付けて 絞ったほうが良いでしょう。

SELECT
Sum(IIf([販売日] Between [三か月前] And [前月末],[売上],0)) AS 直近3か月,
Sum(IIf([販売日] Between [六か月前] And [前月末],[売上],0)) AS 直近6か月
FROM (
SELECT
売上,
IIf(IsDate([販売月] & "/1"), CDate([販売月] & "/1"),Null) AS 販売日,
DateSerial(Year(Date()),Month(Date())-3,1) AS 三か月前,
DateSerial(Year(Date()),Month(Date())-6,1) AS 六か月前,
DateSerial(Year(Date()),Month(Date()),0) AS 前月末
FROM 販売記録テーブル
) AS Q;

なお、[販売月] が、yyyy/mm (例 '2014/06') の形式なら
No.1 さんの 回答のような方法でも動作します。

yyyy/m では
'2014/10' < '2014/5'
ですから、期間が正しく判定できません。

お礼

細部に至りお教えいただきありがとうございます。
データとしては例文と同程度の量なので、クエリも試させていただきます。
何分、まだAccessは初心者同然の為、お教えいただいたコードを理解していくことから始めたいと思います。

回答No.2

007MUKADE

フォームに　最近３ヶ月集計のSubホームと
　　　　　　同上６ヶ月集計のSubホームと　を挿入する
　ってなことでは　ダメすですか？

お礼

回答ありがとうございました！
何とも目から鱗が落ちるご回答でした。サブフォームを使うという発想が出てきませんでした…精進します。
サブフォームを作成しての見栄えも見てみます。ありがとうございました。