Microsoft Access 掲示板

Microsoft Access 掲示板

月別集計クエリ

2 コメント
views
4 フォロー

お世話になります

MT_商品　（商品ID,ほか）
T_生産　（ID,商品ID,生産量,生産日,ほか）

このようなテーブルがあり

商品ID2020年01月2020年02月…2020年01月
001200200…300
0021000…100
00300…0

のような、月ごとの生産量を製品ごとにクロス集計クエリを作ることが目標です。
列数は、閲覧用のフォームから動的に現在から1年前までとか、全データ閲覧など選べるようにしたいです。

Format([生産日],"yyyy\年mm\月")

でまとめて、クロス集計するところまでできました。
生産量のNull値も０に変換できます。

現在わからないところは、
その期間内に生産のないMT_商品ID中の全商品を商品ID行に表示する方法です。
外部結合では解決できないようなので、困っております。

上の表の商品ID003のような表示をするにはどうしたらよいのでしょうか

また、かなり可能性が低いですが、生産が止まった月があった場合、列も表示されません。
これは対処できるのでしょうか？

nokonoko
作成: 2021/01/08 (金) 17:17:28
通報 ...
1
hatena 2021/01/11 (月) 12:14:30

また、かなり可能性が低いですが、生産が止まった月があった場合、列も表示されません。
これは対処できるのでしょうか？

これに対応するには、出力したいすべての年月を格納するテーブルが必要になりますね。
下記のようなテーブルを作成してください。

テーブル名　MT_年月

年月
2020年01月
2020年02月
2020年03月
2020年04月

一つのクエリでは難しいので、複数のクエリを組み合わせて目的に出力になるようにしていきます。

Q_商品_年月

SELECT MT_商品.商品ID, MT_年月.年月
FROM MT_商品, MT_年月;

このクエリですべての商品IDと年月の組み合わせが出力できます。

Q_商品年月別計

SSELECT
 T_生産.商品ID,
 Format([生産日],"yyyy\年mm\月") AS 年月,
 Sum(T_生産.生産量) AS 生産量計
FROM T_生産
GROUP BY T_生産.商品ID, Format([生産日],"yyyy\年mm\月");

このクエリで、商品別年月別の生産量の合計が出力できます。

上記の２つのクエリから下記のようなクロス集計クエリを作成します。
CQ_商品年月別

TRANSFORM Nz(First([生産量計]),0) AS 値
SELECT Q_商品_年月.商品ID
FROM
 Q_商品_年月 LEFT JOIN Q_商品年月別計
 ON (Q_商品_年月.年月 = Q_商品年月別計.年月) AND (Q_商品_年月.商品ID = Q_商品年月別計.商品ID)
GROUP BY Q_商品_年月.商品ID
PIVOT Q_商品_年月.年月;

これでご希望の結果になると思います。

2
nokonoko 2021/01/12 (火) 18:21:54 653a6@54883

ありがとうございました。

ご回答をもとに組み立てることができました。
MT_年月を前もって作成しておくのは、長期運用しにくいので、
自力で解決策を考えてみます。
（困ったら、また質問させていただきます