ACCESS 前年比

質問

下記のようなテーブルがあります。

検収日
品名
数量

クエリにて、下記の様に月別数量の合計を出したいと思います。
どの様にすればよいでしょうか。

月　　　　今年の数量合計　前年の数量合計　前年比(%)
4月　　　1,000　　　　　　　　　2,000　　　　　　　　　50%
５月　　　2,000　　　　　　　　 2,000　　　　　　　　　100%
６月　　　3,000　　　　　　　　 1,500　　　　　　　　　200%
・
・
・
２月
３月
合計　　 6,000　　　　　　　　　5,500　　　　　 　　109%
※例えば、今年度の7月～3月までの数量合計はまだ出ていないので今年度の7月～3月までの欄は空欄、前年比も空欄、合計は今年度の数字が入力してあるところまでの前年比を出したいです。

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文を追加するのでしょうか。

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

回答No.2

30246kiku

＃１です

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

各月のものは表示されなかったという事でしょうか。

確認の仕方になりますが、
提示したものをメモ帳に一旦コピーします。
そして、「★★」「▲▲」部分を置換を使って正式なテーブル/クエリ名に置換えます。
置き換えたものを、クエリのSQLビューに貼り付けて表示を確認してみます。

各SQLビューに貼り付けるものは３つ提示しましたが、

上から、前年分のデータがあったら、その分も表示
なので、合計部分は前年については全部。
一応、これだけで完結するものになります。

その表示では何なんで、クエリ「▲▲」として作っておくものが真中のもの
上のものを加工しやすい内容にしたものです。

その真中のものを使って最終形にするのが下側のものになります。

なお、クエリのデザインではどうなるのかは、SQLビューに貼り付けた後、
表示切り替えして確認してみてください。

※ 解釈間違いがあったらごめんなさい。

お礼

再度のご回答ありがとうございます。

クエリを3つ作成し、完成しました。

完璧です。望んでいたものです。

あとは、体裁よくレポートを作成するだけです。

ご提示されたものをコピペし、▲▲や★★を買えただけですので、完全に理解したわけではないので、ご提示いただいた構文を見ながら勉強したいと思います。