下記のようなテーブルがあります。
検収日
品名
数量
クエリにて、下記の様に月別数量の合計を出したいと思います。
どの様にすればよいでしょうか。
月 今年の数量合計 前年の数量合計 前年比(%)
4月 1,000 2,000 50%
5月 2,000 2,000 100%
6月 3,000 1,500 200%
・
・
・
2月
3月
合計 6,000 5,500 109%
※例えば、今年度の7月~3月までの数量合計はまだ出ていないので今年度の7月~3月までの欄は空欄、前年比も空欄、合計は今年度の数字が入力してあるところまでの前年比を出したいです。
ACCESS 2013です。
No.1ベストアンサー
- 回答日時:
一例ですが
テーブル「T月」を追加します。
内容として、フィールド「月」「順」の2つで以下のデータを作っておきます。
(型は、数値型の整数)
月___順
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 順;
この回答への補足
御回答ありがとうございます。
クエリを作成し、一番上の構文を入れるところまでできました。
月 今年の数量 前年の数量 前年比
合計 10000 20000 50%
このように表示されます。
※クエリ名は仮にQU1としました。
ここまではいいのですが、次のクエリを作成する際、新規作成→クエリデザインを選択するとテーブルの表示とあります。現在テーブルにはT月と★★、クエリにはQU1があります。
どれを選択してクエリ「▲▲」を作成し、真ん中のSQL文を記入するのでしょうか。
クエリ「▲▲」を作成したとします。
作っておいたクエリ「▲▲」を利用して表示します
とありますが、どの様にSQL文を追加するのでしょうか。
教えて教えてで申し訳ないのですが、宜しくお願いいたします。
御回答ありがとうございます。
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
これを入力していませんでした。
入力後、正しく表示されました。
No.2
- 回答日時:
#1です
> 月 今年の数量 前年の数量 前年比
> 合計 10000 20000 50%
> このように表示されます。
各月のものは表示されなかったという事でしょうか。
確認の仕方になりますが、
提示したものをメモ帳に一旦コピーします。
そして、「★★」「▲▲」部分を置換を使って正式なテーブル/クエリ名に置換えます。
置き換えたものを、クエリのSQLビューに貼り付けて表示を確認してみます。
各SQLビューに貼り付けるものは3つ提示しましたが、
上から、前年分のデータがあったら、その分も表示
なので、合計部分は前年については全部。
一応、これだけで完結するものになります。
その表示では何なんで、クエリ「▲▲」として作っておくものが真中のもの
上のものを加工しやすい内容にしたものです。
その真中のものを使って最終形にするのが下側のものになります。
なお、クエリのデザインではどうなるのかは、SQLビューに貼り付けた後、
表示切り替えして確認してみてください。
※ 解釈間違いがあったらごめんなさい。
再度のご回答ありがとうございます。
クエリを3つ作成し、完成しました。
完璧です。望んでいたものです。
あとは、体裁よくレポートを作成するだけです。
ご提示されたものをコピペし、▲▲や★★を買えただけですので、完全に理解したわけではないので、ご提示いただいた構文を見ながら勉強したいと思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 確定申告 個人の確定申告。「医療費のお知らせ」記載内容と医療費控除の明細書【内訳書】に記入する内容の関係 4 2023/03/04 18:41
- Visual Basic(VBA) VBA 連続する名前ごとに集計 3 2022/05/21 18:24
- その他(ビジネス・キャリア) エクセルで一覧表を作るときのレイアウト、月日と項目の配置について相談です。 2 2023/04/11 09:37
- 通信費・水道光熱費 電気代の値上がりがやばいです。一人暮らしで1Kの家に住んでいますが、1万超えてます。 暖房は一切使わ 5 2023/01/30 14:35
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- 中学校 減少率計算をお願いします。 今年 1月6740 2月6161 3月4578 前年 1月7325 2月 2 2022/10/01 17:49
- 政治 第26回参議院議員選挙(令和4年6月22日公示・7月10日投開票予定)。どの党に投票しますか? 7 2022/06/09 12:57
- Excel(エクセル) エクセルで最初に値が入っているセルを見つける方法はありますか? 2 2023/07/18 14:58
- 投資・株式の税金 一般口座で同一銘柄の総平均法のことで 1 2023/02/27 22:08
- 年末調整 令和4年分 給与所得者の基礎控除申告書 兼 給与所得者の配偶者控除等申告書 兼所得金額調整控除申告書 3 2022/11/04 03:46
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelで負の数を足さずに0以上...
-
4で割ると1余り、5で割ると2余...
-
Excelでかなり横長の表を作って...
-
霊波之光、エホバ、顕正会の悪...
-
ACCESS 前年比
-
エクセルで右下に合計を出す方法?
-
2592の正の約数の個数と、その...
-
天秤の問題
-
A〜Eの5人で数学のテスト結果を...
-
ひとつのセル内に、日付の関数...
-
中学数学をなめてはいけないな...
-
トランプを5枚選んで46以上にな...
-
数Ⅲです。 赤線のところがわか...
-
カウントダウンの数え方
-
大,中,小3個のさいころを投げ...
-
ケーキの9等分
-
1から9までの番号をつけた9枚の...
-
【数学】反比例、逆数、逆比例...
-
高1です!次の問題を分かりやす...
-
高一数学です。 α3乗+β3乗+γ3...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelで負の数を足さずに0以上...
-
以下の問題ってどう解くのです...
-
天秤の問題
-
2行おきの合計、VBAでど表現す...
-
中学数学をなめてはいけないな...
-
VBAで各列の"+"と"o"の合計数を...
-
SPIの問題について質問です。 P...
-
千円引きと20%引き
-
トランプの数値の合計数を計算...
-
Excelでかなり横長の表を作って...
-
Excelにて、フッターにシートの...
-
Excelの関数、マイナスになった...
-
2桁のチェックデジット求め方...
-
エクセルVBAで複数の図に名前を...
-
エクセルで右下に合計を出す方法?
-
4で割ると1余り、5で割ると2余...
-
リンゴ5個ミカン3個を購入し合...
-
異なる4つの数字を選び作れる整...
-
A〜Eの5人で数学のテスト結果を...
-
二項係数の和で、二項定理を使...
おすすめ情報