ACCESS 前年比を計算する方法 - 月別数量合計

質問

• ACCESS 2013を使用して、月別数量の合計を計算する方法を教えてください。
• テーブルの検収日、品名、数量の情報を使用して、今年の数量合計と前年の数量合計をそれぞれ計算し、月別の前年比を算出します。
• 具体的な処理方法として、クエリを使用して月別数量の合計を計算し、集計結果をテーブルに表示することができます。

回答No.1

30246kiku · ベストアンサー

一例ですが

テーブル「T月」を追加します。
内容として、フィールド「月」「順」の２つで以下のデータを作っておきます。
（型は、数値型の整数）

月___順
1____10
2____11
3____12
4_____1
5_____2
6_____3
7_____4
8_____5
9_____6
10____7
11____8
12____9

元々のテーブル名を「★★」と仮定します。
入っているデータの「検収日」最大の年を今年と解釈します。
また、「検収日」は日付/時刻型とします。
クエリのSQLビューに以下を記述して表示を確かめます。

SELECT First(Q1.月) AS 月,
Sum(IIF(Q2.年=Q1.年度, Q1.数量)) AS 今年の数量合計,
Sum(IIF(Q2.年=Q1.年度+1, Q1.数量)) AS 前年の数量合計,
IIF(Not IsNull(今年の数量合計),
Format(今年の数量合計/Nz(前年の数量合計,今年の数量合計),'0.00 %')) AS 前年比
FROM
(SELECT Q11.月 & '月' AS 月, 順, 検収日, 数量, 年度 FROM T月 AS Q11 LEFT JOIN
(SELECT *, Year(DateAdd('m', -3, 検収日)) AS 年度 FROM ★★) AS Q12
ON Q11.月=Month(Q12.検収日)
UNION ALL
SELECT '合計', 13, 検収日, 数量, Year(DateAdd('m', -3, 検収日)) FROM ★★) AS Q1,
(SELECT Year(DateAdd('m', -3, Max(検収日))) AS 年 FROM ★★) AS Q2
GROUP BY Q1.順
ORDER BY Q1.順;

これでは、表示が今一つ？（今年が空欄でも、前年にデータがあれば表示）なので、
以下内容で、クエリ「▲▲」を作っておいて

SELECT First(Q1.月) AS 月, Q1.順,
Sum(IIF(Q2.年=Q1.年度, Q1.数量)) AS 今年の数量合計,
Sum(IIF(Q2.年=Q1.年度+1, Q1.数量)) AS 前年の数量合計,
IIF(Not IsNull(今年の数量合計),
Format(今年の数量合計/Nz(前年の数量合計,今年の数量合計),'0.00 %')) AS 前年比
FROM
(SELECT Q11.月 & '月' AS 月, 順, 検収日, 数量, 年度 FROM T月 AS Q11 LEFT JOIN
(SELECT *, Year(DateAdd('m', -3, 検収日)) AS 年度 FROM ★★) AS Q12
ON Q11.月=Month(Q12.検収日)) AS Q1,
(SELECT Year(DateAdd('m', -3, Max(検収日))) AS 年 FROM ★★) AS Q2
GROUP BY Q1.順
ORDER BY Q1.順;

作っておいたクエリ「▲▲」を利用して表示します

SELECT 月, 今年の数量合計, 前年の数量合計, 前年比 FROM
(SELECT 月, 順, 今年の数量合計,
IIF(Not IsNull(今年の数量合計),▲▲.前年の数量合計) AS 前年の数量合計,
前年比 FROM ▲▲
UNION ALL
SELECT '合計', 13, Sum(今年の数量合計) AS A,
Sum(IIF(Not IsNull(今年の数量合計),前年の数量合計)) AS B,
IIF(Not IsNull(A),Format(A/Nz(B,A),'0.00 %')) FROM ▲▲
) AS Q1
ORDER BY 順;

お礼

御回答ありがとうございます。

T月のテーブルを作成しただけで、

月___順
1____10
2____11
3____12
4_____1
5_____2
6_____3
7_____4
8_____5
9_____6
10____7
11____8
12____9

これを入力していませんでした。
入力後、正しく表示されました。

補足

御回答ありがとうございます。

クエリを作成し、一番上の構文を入れるところまでできました。

月　　今年の数量　　前年の数量　前年比
合計　　　10000 　　　 20000　 　　　 50%
このように表示されます。

※クエリ名は仮にQU1としました。

ここまではいいのですが、次のクエリを作成する際、新規作成→クエリデザインを選択するとテーブルの表示とあります。現在テーブルにはT月と★★、クエリにはQU1があります。

どれを選択してクエリ「▲▲」を作成し、真ん中のSQL文を記入するのでしょうか。

クエリ「▲▲」を作成したとします。

作っておいたクエリ「▲▲」を利用して表示します
とありますが、どの様にSQL文を追加するのでしょうか。

教えて教えてで申し訳ないのですが、宜しくお願いいたします。