Access クエリ SQL レポート作成

Question

Accessでクエリを作成し、レポートを表示させたい。
現在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週);

クエリを作成しています。

上記のコードは、前回質問し教えて貰ったコードとなります。
それまでは、VBAで無理やり回して毎回新規テーブルで作成していました。
最終結果をエクセルに出力して、手修正で完成品を作っています。
今回レポートを使用して、同じ結果になる事が最終の目的です。

上記の画像を横に並べた状態をレポートで表示させたいです。
セル色の黄色部分は上記のSQLに無い項目です。
セル色の青色は、当年1週・・・昨年1週・・・の名が当年1週(数量)・・・昨年1週(数量)・・・と変更しています。
当年1週(金額)の計算方法は、当年1週(数量)にT_実績のフィールドに売り_単価としてありますので数量に掛けて計算したいです。

またレポートは画像のように、連続して同じCDや名称時は表示させない用にしたいです。
売先が変わる毎に、最終行に合計を表示させたいです。

2021/7/19追記
まず、当年の数量と金額の表示をさせようとしました。

数量のSQLを

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

金額のSQLを

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

としました。
次に別々に作成したSQLを一つにするために

TRANSFORM (Sum([T_実績.数量]) AS 数量の合計,Sum([T_実績.数量]*[T_実績.売り_単価]) AS 金額の合計)

や

TRANSFORM IN(Sum([T_実績.数量]) AS 数量の合計,Sum([T_実績.数量]*[T_実績.売り_単価]) AS 金額の合計)

を試しましたが、エラーとなりました。

また、PIVOTは

PIVOT IIf(T_実績.売立_年=2021,"当年","昨年") & DateDiff("ww",DateSerial(T_実績.売立_年,T_実績.売立_月,1),[売立日])+1 & "週" In (当年1週(数量),当年2週(数量),当年3週(数量),当年4週(数量),当年5週(数量),当年6週(数量),昨年1週(数量),昨年2週(数量),昨年3週(数量),昨年4週(数量),昨年5週(数量),昨年6週(数量),当年1週(金額),当年2週(金額),当年3週(金額),当年4週(金額),当年5週(金額),当年6週(金額),昨年1週(金額),昨年2週(金額),昨年3週(金額),昨年4週(金額),昨年5週(金額),昨年6週(金額));

としています。

まず、数量と金額の表示をさせれる用になった後に合計や目標の値を表示させれる用になりたいです。

とりあえず、今は数量と金額のクエリをさらに判定コードを作成して二個を1個にまとめています。

Comments

[m.ts10806]

まず簡単でも良いので、取得行数出力するレポートを作って見るところからではないでしょうか。
それとも、質問ではなく作業依頼でしたか？

[kyousuke8957]

まず現在のSQLで作成したクエリを出力すると、連続する項目を空白にする方法が分りません。
次に売先コードが変わる度に合計を挿入したいが、変わる度に挿入・表示する方法が分りません。

クエリ作成については、テーブルから複数のクエリを作って最終的にクエリを纏めるイメージだったのでひとつのクエリでの実行方法が分らないです。

VBAでテーブルから取得して、毎回新規テーブルを作成しVBAで計算した結果を書き込む事はできますが処理に時間が掛かるのと、DBとして処理の方法としては間違っているかと思い質問しています。

[sazi]

一度の質問に詰め込み過ぎで、分かりませんだけでは、丸投げにしか取られません。

> まず現在のSQLで作成したクエリを出力すると、連続する項目を空白にする方法が分りません。
> 次に売先コードが変わる度に合計を挿入したいが、変わる度に挿入・表示する方法が分りません。

これは、レポートの話です。

> クエリ作成については、テーブルから複数のクエリを作って最終的にクエリを纏めるイメージだったのでひとつのクエリでの実行方法が分らないです。

こちらは、出力したい項目でテーブルを作成しておき、後でレコードソースをクエリーに変更すれば良いので、切り離して考える事も出来ます。
そもそも、レポートのレイアウト的に１ページに収まるかどうか確認済みなのでしょうか？
収まらない場合にはどのような出力形式にするか検討も必要でしょうし。

[m.ts10806]

もっと聞きたいことをスポットに絞られたほうが良いですね。
全体の中の進捗からここがあれば少し進めるとか見えるとか
要点を絞る
質問は編集できますので内容調整してください。

[kyousuke8957]

申し訳ございません、前回の質問が部分的な質問で尚且つ作り方がそもそも間違っていたので、
教えて頂いた内容が無駄になったり二度手間になったりと思い、完成させたい物を全て詰め込んだ形となりました。

レポートの出力ですが、出力項目がかなり多いですが文字サイズで調整したと思っています。
現在は、エクセルでA3用紙横向きで余白狭いで対応しています。
Accessでは、合計の出力や空白にする方法が分らなかった為一度エクセルに出力している為、実際にレポート表示を行えていません。
横は1ページに収めて、縦は複数ページを想定しています。

[m.ts10806]

質問は編集できます。

Answer 1

先ずは調べてみましょうよ。

まず現在のSQLで作成したクエリを出力すると、連続する項目を空白にする方法が分りません。

「重複データ非表示」プロパティで対応できます。
Accessのレポートで重複するデータを非表示にする方法

次に売先コードが変わる度に合計を挿入したいが、変わる度に挿入・表示する方法が分りません。

グループヘッダー/フッターで対応できます。
Accessのレポートでグループごとに金額の合計を印刷する方法
※但し、レポート内でSum()などの合計を使用する場合には、数に限りがあります。
余りに多い場合には、クエリーなどで計算する必要があります。

クロス集計に集計項目を追加したいとの事ですが、クロス集計は計算する値は一つしか指定できません。
クエリーを繋ぐのでも良いですけど、集計関数内でiif()などを使用し、条件に一致しない値を0にすれば、クロス集計ではなく、集計クエリーで可能です。

select
 sum(iif([条件], [値], 0))
from ～

追記

次に別々に作成したSQLを一つにするために・・・エラーになりました。

それぞれをクエリーとして保存し、それを結合するクエリーが希望する形になります。

Comments

[kyousuke8957]

ありがとうございます。
重複データの非表示について調べてみます。
またレポートで合計は20～30件程度になるかと思います。

金曜まで纏まった時間が無いので、試すのに少々時間がかかりますが試してみます。

[kyousuke8957]

教えて頂いて、そのまま使用していた下記SQLのPIVOTの関数を理解出来ていないので、まず分解して個別に見たくて別クエリを作成しました。

```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週);
```

結果1: DateSerial([T_実績.売立_年],[T_実績.売立_月],2)
結果2: [売立日]
結果3: DateDiff("ww",[結果1],[結果2],[売立日])+1
結果4: DateDiff("ww",[結果1],[結果2],[売立日])+1 & "週"
と表示させたら、結果3と結果4が#Func!と表示されます。
これは、ひとつの式にした時は問題無く実行できるので、気にしなくていい結果でしょうか？
また、DateSerialの日の引数が2日で固定しているのが理解できませんでした。

```SQL
SELECT T_実績.売先_CD, T_実績.仕入先_CD, T_実績.売立日, T_実績.売立_年, T_実績.売立_月, DateSerial([T_実績.売立_年],[T_実績.売立_月],2) AS 結果1, T_実績.[売立日] AS 結果2, DateDiff("ww",[結果1],[結果2],[売立日])+1 AS 結果3, DateDiff("ww",[結果1],[結果2],[売立日])+1 & "週" AS 結果4
FROM T_実績
WHERE (((T_実績.売立_年)=2021));

[sazi]

> と表示させたら、結果3と結果4が#Func!と表示されます。
同じレベル内でのselectのエイリアスは参照できません。

> また、DateSerialの日の引数が2日で固定しているのが理解できませんでした。
質問がそうなっていたからです。

[kyousuke8957]

結果3: DateDiff("ww",[結果1],[結果2],[売立日])+1
結果4: DateDiff("ww",[結果1],[結果2],[売立日])+1
が間違っていたみたいです。
結果3: DateDiff("ww",[結果1],[結果2])+1
結果4: DateDiff("ww",[結果1],[結果2])+1
へ変更致しました。

#Func!から数値が反映されました。

結果4は、売立日がその月の何週目かが反映されているフィールドと認識していますが、合っていますか？
また、DateDiffで"ww"で週の単位で差を表示していると認識しています。
私が、質問の内容として指定した結果が2日になっていると思いますが、
結果1と結果2を週で比較しているとして、結果1の日付を1日でなくて2日で固定しているのが分かりません。

[sazi]

そもそも、元の質問で、
> DateDiff("ww",DateSerial(Year([売立日]),Month([売立日]),2),[売立日])+1 AS 週目
となっているところについては、質問のコメントで何をしようとしているのか質問しましたけど、大丈夫ですとの事だったので、記述を簡潔にしたのみで、特に内容について気にしていません。

> 日付を1日でなくて2日で固定しているのが分かりません。
そうですよね。
なんでそういうことしているのか聞こうとしたけど、「大丈夫です。」との事だったので、敢えて触れていません。

> 結果4は、売立日がその月の何週目かが反映されているフィールドと認識していますが、合っていますか？

DateDiffの()仕様は分かっておられるんでしょうから、あなたの意図が反映されているだけです。

[kyousuke8957]

申し訳ないです、おそらく私の勘違いです。
調べて出てきた物をほとんど貼り付で使用していて、その時は3個目の引数が開始曜日を指定していると認識していた為です。

[kyousuke8957]

追記ありがとうございます。
では、現在作成した数量クエリと金額クエリを繋いで運用致します。
また合計のクエリを新たに作成しこれもクエリで繋いでみます。

[sazi]

クロス集計しない形も考えられますが、文字だけのやり取りで上手くいくような感じがしないので、先ずは出来ているもので行った方が良いと思います。

[kyousuke8957]

ありがとうございます。
SQLは色々と出来そうですが、難しいです。

とりあえず、一度解決済みとさせて頂きました。