Access クエリを横方向に結合

Question

テーブルから、クロス集計クエリを作成した後に結合をしたい。

クロス集計クエリ1とクロス集計クエリ2を作成。
[年度,売cd,売り先名,仕入cd,仕入先名,種類,1週,2週,3週,4週]
クロス集計クエリ1とクロス集計クエリ2の違いは、年度の抽出条件が2020年と2021年。

クロス集計クエリ1とクロス集計クエリ2を、横方向に結合したい。
結合条件は、売cd,仕入cd,種類が一致した物を横方向に結合。
片方にしか存在しないものも表示する。

完成クエリのイメージは以下
[売cd,売り先名,仕入cd,仕入先名,種類,当年1週,当年2週,当年3週,当年4週.昨年1週,昨年2週,昨年3週,昨年4週]

現在は、クロス集計クエリ1とクロス集計クエリ2をVBAで読み込みループで一致する項目を探して新規テーブルを作成し書き込みで処理。
クエリやSQLで解決を行いたい。

よろしくお願いいたします。

以下追記 20210710
クエリで実績テーブルから必要情報のみを抜き出すクエリを作成しています。
その際に、月曜開始で何週目かを判定させています。
下記SQL

SELECT T_実績.売立日, T_実績.売先_CD, T_得意先M.得意先名称, T_実績.仕入先_CD, T_仕入先M.仕入先名称, T_実績.品目_CD, T_品目M.品名, T_実績.登録品目名, T_実績.数量, T_実績.売り_単価, T_実績.仕入_単価, DateDiff("ww",DateSerial(Year([売立日]),Month([売立日]),2),[売立日])+1 AS 週目, T_実績.規格
FROM T_品目M INNER JOIN (T_仕入先M INNER JOIN (T_得意先M INNER JOIN T_実績 ON T_得意先M.得意先ｺｰﾄﾞ = T_実績.売先_CD) ON T_仕入先M.仕入先ｺｰﾄﾞ = T_実績.仕入先_CD) ON T_品目M.[コード] = T_実績.品目_CD
WHERE (((T_実績.売立_年)=2020) AND ((T_実績.売立_月)=6));

条件を2020で[Q_昨年]と2021で[Q_当年]二つのクエリを作成しています。

[Q_昨年]と[Q_当年]をそれぞれクロス集計クエリで週目を計算しています。
下記SQL

TRANSFORM Sum(Q_当年.数量) AS 数量の合計
SELECT Q_当年.売先_CD, Q_当年.得意先名称, Q_当年.仕入先_CD, Q_当年.仕入先名称, Q_当年.品目_CD, Q_当年.品名, Q_当年.規格
FROM Q_当年
GROUP BY Q_当年.売先_CD, Q_当年.得意先名称, Q_当年.仕入先_CD, Q_当年.仕入先名称, Q_当年.品目_CD, Q_当年.品名, Q_当年.規格
PIVOT Q_当年.週目;

[Q_昨年_クロス]と[Q_当年_クロス]
を結合して、昨年と当年を横に並べたいです。

Comments

[sazi]

> クロス集計クエリ1とクロス集計クエリ2の違いは、年度の抽出条件が2020年と2021年。
当月と前年同月の比較なら、抽出条件は年月ですよね。
1～４週としていますが、月をどのような条件で4週に分けているんでしょう？

これらを明らかにするのに、現在のクエリーの内容も質問に追記されて下さい。

[kyousuke8957]

まず、クエリで実績テーブルから必要情報のみを抜き出すクエリを作成しています。
その際に、月曜開始で何週目かを判定させています。
下記SQL
---------------------
SELECT T_実績.売立日, T_実績.売先_CD, T_得意先M.得意先名称, T_実績.仕入先_CD, T_仕入先M.仕入先名称, T_実績.品目_CD, T_品目M.品名, T_実績.登録品目名, T_実績.数量, T_実績.売り_単価, T_実績.仕入_単価, DateDiff("ww",DateSerial(Year([売立日]),Month([売立日]),2),[売立日])+1 AS 週目, T_実績.規格
FROM T_品目M INNER JOIN (T_仕入先M INNER JOIN (T_得意先M INNER JOIN T_実績 ON T_得意先M.得意先ｺｰﾄﾞ = T_実績.売先_CD) ON T_仕入先M.仕入先ｺｰﾄﾞ = T_実績.仕入先_CD) ON T_品目M.[コード] = T_実績.品目_CD
WHERE (((T_実績.売立_年)=2020) AND ((T_実績.売立_月)=6));
---------------------

条件を2020で[Q_昨年]と2021で[Q_当年]二つのクエリを作成しています。

[Q_昨年]と[Q_当年]をそれぞれクロス集計クエリで週目を計算しています。
下記SQL
---------------------
TRANSFORM Sum(Q_当年.数量) AS 数量の合計
SELECT Q_当年.売先_CD, Q_当年.得意先名称, Q_当年.仕入先_CD, Q_当年.仕入先名称, Q_当年.品目_CD, Q_当年.品名, Q_当年.規格
FROM Q_当年
GROUP BY Q_当年.売先_CD, Q_当年.得意先名称, Q_当年.仕入先_CD, Q_当年.仕入先名称, Q_当年.品目_CD, Q_当年.品名, Q_当年.規格
PIVOT Q_当年.週目;
---------------------

[Q_昨年_クロス]と[Q_当年_クロス]
を結合して、昨年と当年を横に並べたいです。

[sazi]

質問は編集できるので、SQLは質問に追記して下さい。
本題ではありませんが、週目について、カレンダーでの何週目かで比較していますが、年が変われば各週の日数は違いますが、その比較でいいんでしょうか？

[kyousuke8957]

追記致しました。
何週目かは下記で行っています。
DateDiff("ww",DateSerial(Year([売立日]),Month([売立日]),2),[売立日])+1

[sazi]

どのようなではなく、比較される週の日数が異なる場合があるけどそれでいいの？です。
比較するなら7日区切りで比較とかが普通だと思います。
その場合４週目は月末までとかにしたりしますけどね。

[kyousuke8957]

日数が異なっていて大丈夫です。

教えて頂いたコードは、本日は試せないので明日行います。

Answer 1

一般的に前年当月比は縦に並べて比較するものですが、その場合は以下の様になります。
（年月を集計単位に加える）

TRANSFORM Sum(T_実績.数量) AS 数量の合計
SELECT T_実績.売先_CD, T_得意先M.得意先名称, T_実績.仕入先_CD, T_仕入先M.仕入先名称, T_実績.品目_CD, T_品目M.品名, T_実績.規格, T_実績.売立_年, T_実績.売立_月
FROM  ((T_実績 INNER JOIN T_得意先M ON T_得意先M.得意先ｺｰﾄﾞ = T_実績.売先_CD
      ) INNER JOIN T_仕入先M ON T_仕入先M.仕入先ｺｰﾄﾞ = T_実績.仕入先_CD
      ) INNER JOIN T_品目M ON T_品目M.[コード] = T_実績.品目_CD
WHERE 売立_年 In (2020,2021) AND 売立_月=6
GROUP BY T_実績.売先_CD, T_得意先M.得意先名称, T_実績.仕入先_CD, T_仕入先M.仕入先名称, T_実績.品目_CD, T_品目M.品名, T_実績.規格, T_実績.売立_年, T_実績.売立_月
PIVOT DateDiff("ww",DateSerial(T_実績.売立_年,T_実績.売立_月,2),[売立日])+1 & "週";

希望はこの年月部分を横軸に展開という事ですから、上記の集計単位から年月を外し、PIVOT部分にその編集を加えます。

TRANSFORM Sum(T_実績.数量) AS 数量の合計
SELECT T_実績.売先_CD, T_得意先M.得意先名称, T_実績.仕入先_CD, T_仕入先M.仕入先名称, T_実績.品目_CD, T_品目M.品名, T_実績.規格
FROM  ((T_実績 INNER JOIN T_得意先M ON T_得意先M.得意先ｺｰﾄﾞ = T_実績.売先_CD
      ) INNER JOIN T_仕入先M ON T_仕入先M.仕入先ｺｰﾄﾞ = T_実績.仕入先_CD
      ) INNER JOIN T_品目M ON T_品目M.[コード] = T_実績.品目_CD
WHERE 売立_年 In (2020,2021) AND 売立_月=6
GROUP BY T_実績.売先_CD, T_得意先M.得意先名称, T_実績.仕入先_CD, T_仕入先M.仕入先名称, T_実績.品目_CD, T_品目M.品名, T_実績.規格
PIVOT IIf(T_実績.売立_年=2021,"当年","昨年") & DateDiff("ww",DateSerial(T_実績.売立_年,T_実績.売立_月,2),[売立日])+1 & "週";

PIVOTの並びはorderできませんので、やるとしたらInで固定見出しを設定します。
※固定見出しはPIVOTで編集される内容に一致させる必要があります。

PIVOT IIf(T_実績.売立_年=2021,"当年","昨年") & DateDiff("ww",DateSerial(T_実績.売立_年,T_実績.売立_月,2),[売立日])+1 & "週" in("当年1週","当年2週"・・・)

Comments

[kyousuke8957]

教えて頂いた、下記のSQLで希望の事が出来ました。
今回使用したSQLの関数については後程調べて、自身で使えるようにしたいと思います。
クエリを複数用意して繋ぐものだと思っていたのですが、一つのクエリで行えるとも思っていませんでした。
今回教えて頂いた結果を、さらにクエリで繋ぐつもりでしたが一つのクエリで実行可能でしょうか？

・今回教えて頂いたコード
TRANSFORM Sum([T_実績.数量]) AS 数量の合計
SELECT T_実績.売先_CD, T_得意先M.得意先名称, T_実績.仕入先_CD, T_仕入先M.仕入先名称, T_実績.品目_CD, T_品目M.品名, T_実績.規格
FROM ((T_実績 INNER JOIN T_得意先M ON T_得意先M.得意先ｺｰﾄﾞ = T_実績.売先_CD
) INNER JOIN T_仕入先M ON T_仕入先M.仕入先ｺｰﾄﾞ = T_実績.仕入先_CD
) INNER JOIN T_品目M ON T_品目M.[コード] = T_実績.品目_CD
WHERE 売立_年 In (2020,2021) AND 売立_月=6
GROUP BY T_実績.売先_CD, T_得意先M.得意先名称, T_実績.仕入先_CD, T_仕入先M.仕入先名称, T_実績.品目_CD, T_品目M.品名, T_実績.規格
PIVOT IIf(T_実績.売立_年=2021,"当年","昨年") & DateDiff("ww",DateSerial(T_実績.売立_年,T_実績.売立_月,2),[売立日])+1 & "週" in(当年1週,当年2週,当年3週,当年4週,当年5週,当年6週,昨年1週,昨年2週,昨年3週,昨年4週,昨年5週,昨年6週)

上記の結果に当月売上目標数,当月売上目標金額,当年売り上げ合計,昨年売り上げ合計の4つ追加して、
売先_CD, 得意先名称, 仕入先_CD, .仕入先名称, 品目_CD, T_品目M.品名, 規格, 当月売上目標金額,当月売上目標数 ,当年1週,当年2週,当年3週,当年4週,当年5週,当年6週,当年売り上げ合計,昨年1週,昨年2週,昨年3週,昨年4週,昨年5週,昨年6週,昨年売り上げ合計
のクエリを作成。
T_実績テーブルには、売り_単価フィールドがあり当年の数量合計に単価を掛けて当年売り上げ合計を表示。
その後レポートで

L0001,A売り先,P0001,A仕入先,I001,品目A,規格A,1000000,100,50,100,150,150,100,0,550000,200,200,200,200,200,0,1000000
, 　　　,P0001,A仕入先,I002,品目B,規格B,1000000,100,50,100,150,150,100,0,550000,200,200,200,200,200,0,1000000
, 　　　,P0002,B仕入先,I003,品目C,規格C,1000000,100,50,100,150,150,100,0,550000,200,200,200,200,200,0,1000000
合計 3000000 1650000 3000000

L0001,B売り先,P0001,A仕入先,I001,品目A,規格A,1000000,100,50,100,150,150,100,0,550000,200,200,200,200,200,0,1000000
, 　　　, , ,I002,品目B,規格B,1000000,100,50,100,150,150,100,0,550000,200,200,200,200,200,0,1000000
, 　　　,P0002,B仕入先,I003,品目C,規格C,1000000,100,50,100,150,150,100,0,550000,200,200,200,200,200,0,1000000
, 　　　,P0003,C仕入先,I003,品目C,規格C,1000000,100,50,100,150,150,100,0,550000,200,200,200,200,200,0,1000000
合計 4000000 2200000 4000000

上記のように表示させ、A4用紙に並べて印刷。

可能なら、売先cdが変わる所で上手く改ページをしたい。
また連続して同じcdの時は空白で表示。
売先cd毎に最終行に売り上げの合計を表示。

今回の質問事態は解決出来ているので、一度閉じて再度投稿した方がよろしいでしょうか？

[sazi]

> 一度閉じて再度投稿した方がよろしいでしょうか？
そうですね。
どこが追加質問なのかよく分かりませんし。

[sazi]

> 当月売上目標数,当月売上目標金額,当年売り上げ合計,昨年売り上げ合計の4つ追加して

inで見出しを固定にしていいのなら、クロス集計でなくとも、上記を含めて集計クエリーでよいのではないかと思いますが。

[kyousuke8957]

改めて、質問投稿させて頂きます。

Answer 2

元のテーブルのフィールド構成が不明なので、外しているかも知れませんが、
２つのクロス集計クエリを結合するより、
最初から一つのクロス集計クエリで作成した方がシンプルにできそうです。
下記のような感じで。

売cd, 売り先名, 仕入cd, 仕入先名, 種類 を行見出しに

列見出しは下記の式を

IIf([年度]=Year([Date]),"当年","昨年") & [週番号] & "週"

年度 の Where条件を
In (Year([Date]), Year([Date])-1)

フィールド名等は推測ですので実際に合わせて変更してください。

Comments

[kyousuke8957]

昨年と当年を横並びにして、比べたいのですが一つのクロス集計で両方を表示させることは可能でしょうか？

[hatena19]

可能です。
saziさんから具体的なSQLが提示されているので参考にしてください。
私の回答と考えかたは同じです。