複数列フィールド値のクロス集計

strSQL = strSQL & " transform sum(売上) as 金額 "
strSQL = strSQL & " SELECT "
strSQL = strSQL & " 支店番号 "
strSQL = strSQL & " FROM [顧客マスタ$] "
strSQL = strSQL & " WHERE "
strSQL = strSQL & " 日付 "
strSQL = strSQL & " BETWEEN #2018/04/01# AND #2018/09/30#"
strSQL = strSQL & " GROUP BY 支店番号 "
strSQL = strSQL & " PIVOT month(日付) IN (4,5,6,7,8) "
これは通常のクロス集計です。これに加えて、
列に４月件数　４月合計　5月件数　5月合計
というふうにしたいのですが、画像も添付しました。
わかる方おしえていただけませんでしょうか。
transform COUNT(売上) as 金額
transform sum(売上) as 金額
どうもうまくいきません。

質問者からの補足コメント

• 

  Excelの標準モジュール
  strSQL = "TRANSFORM SUM(IIF(S=1,1,売上)) AS 値"
  strSQL = "SELECT 支店番号 FROM ("
  strSQL = "SELECT 1 AS S, * FROM [顧客マスタ$]"
  strSQL = "Union ALL"
  strSQL = "SELECT 2"
  strSQL = "SELECT 2, * FROM [顧客マスタ$]) AS Q1"
  strSQL = "WHERE 日付 BETWEEN #2018/04/01# AND #2018/09/30#"
  strSQL = "GROUP BY 日付番号"
  strSQL = "PIVOT Month(日付) & "月"& IIF(S=1,"件数","合計");
  　　　　　　　　　　　　　　　　↑ここが恐らくおかしいのかも
  恐らく、なにかをなおせば成功する

  No.3の回答に寄せられた補足コメントです。 補足日時：2019/05/05 18:53

• 

  助けてください
  いろいろためしましたが
  strSQL = "PIVOT Month(日付) & "月"& IIF(S=1,"件数","合計");"

    補足日時：2019/05/05 19:01

• 

  strSQL = "TRANSFORM SUM(IIF(S=1,1,売上)) AS 値 "
  strSQL = strSQL & "SELECT 支店番号 FROM ( "
  修正しました。strSQL = strSQL & "PIVOT Month(日付) & "月" & IIF(S=1,"件数","合計");
  この部分の文字の色が変わりじっこうできません

  No.5の回答に寄せられた補足コメントです。 補足日時：2019/05/05 20:35

• 

  こちらで問題なくなりました。
  strSQL = strSQL & "PIVOT Month(日付) & '月' & IIF(S=1,'件数','合計');"
  
  strSQL = "TRANSFORM SUM(IIF(S=1,1,売上)) AS 値 "構文エラーとなります。
  恐れ入りますが　おしえてくれませんでしょうか

  No.6の回答に寄せられた補足コメントです。 補足日時：2019/05/05 22:14

No.7 ベストアンサー 回答者： 30246kiku 回答日時： 2019/05/06 06:46

＃６です

> strSQL = "TRANSFORM SUM(IIF(S=1,1,売上)) AS 値 "構文エラーとなります。
> 恐れ入りますが　おしえてくれませんでしょうか

各行間は、少なくとも空白が１つはある
" は ' に変更して良さそう

ということであれば、わかりません

Access の SQLビュー で作成・それなりに表示されたものを、
テーブル名のみ変更して提示しただけです


※ ブログには、サンプルファイル付けていたと思うけど･･･

この回答へのお礼

いろいろためしましたが私のやり方か
だめでした。ありがとうございました

お礼日時：2019/05/06 23:03

No.8 回答者： めぐみん_ 回答日時： 2019/05/06 21:46

横から失礼いたします。

＞もう一度言います
＞> Access のクエリの SQLビュー をVBA 用に変更した経験は無いのですか？

過去の質問をいくつか見てきましたが、ExcelVBAによりADOでBookやCSVファイルへ接続する質問になってました。
個人的にですがAccessの経験はないように感じました。

No.6 回答者： 30246kiku 回答日時： 2019/05/05 21:02

＃５　です

もう一度言います

> Access のクエリの SQLビュー をVBA 用に変更した経験は無いのですか？


クエリの SQLビュー に記述した、例えば　"件数"
VBA の文字列に指定する時には
""件数"" とするか '件数' にします

strSQL = strSQL & "PIVOT Month(日付) & "月" & IIF(S=1,"件数","合計");

↓

strSQL = strSQL & "PIVOT Month(日付) & ""月"" & IIF(S=1,""件数"",""合計"");"

とか

strSQL = strSQL & "PIVOT Month(日付) & '月' & IIF(S=1,'件数','合計');"


文字列内の " の扱いの基本と思います

No.5 回答者： 30246kiku 回答日時： 2019/05/05 19:39

＃４です

良く見たら、空白以前の問題ですね

strSQL = "TRANSFORM SUM(IIF(S=1,1,売上)) AS 値 "
strSQL = "SELECT 支店番号 FROM ( "

↓

strSQL = "TRANSFORM SUM(IIF(S=1,1,売上)) AS 値 "
strSQL = strSQL & "SELECT 支店番号 FROM ( "

No.4 回答者： 30246kiku 回答日時： 2019/05/05 19:29

＃３です

Access のクエリの SQLビュー をVBA 用に変更した経験は無いのですか？

簡単に言えば、

文字列を結合する際には、各行 間に、空白を入れます
提示されたものは、先頭に空白有るようですが？？？

> strSQL = "TRANSFORM SUM(IIF(S=1,1,売上)) AS 値"
> strSQL = "SELECT 支店番号 FROM ("

↓

strSQL = "TRANSFORM SUM(IIF(S=1,1,売上)) AS 値 "
strSQL = "SELECT 支店番号 FROM ( "


でないと、

･･･値SELECT ････ って、何？？？

･･･値 SELECT ･･･


※ 上記は、各文字列の後に空白入れてみましたが････

No.3 回答者： 30246kiku 回答日時： 2019/05/05 17:00

クエリの SQLビューに以下を記述し、表示してみてどうなりますか

TRANSFORM SUM(IIF(S=1,1,売上)) AS 値
SELECT 支店番号 FROM (
SELECT 1 AS S, * FROM [顧客マスタ$]
UNION ALL
SELECT 2, * FROM [顧客マスタ$]) AS Q1
WHERE 日付 BETWEEN #2018/04/01# AND #2018/09/30#
GROUP BY 支店番号
PIVOT Month(日付) & "月" & IIF(S=1,"件数","合計");


この方法での説明は、ブログでやってますが････
複数列への仕方とか、列合計の組み込み方とか････
でも、URL 記述すると営利目的だか何だかで、事務局に削除されるので････
似たような回答した、以下から辿ってみるとか････

Access2013を使っています。 クロス集計の値が
https://detail.chiebukuro.yahoo.co.jp/qa/questio …

No.2 回答者： komugi3333 回答日時： 2019/05/04 23:50

私もあまり詳しくはないのですみませんが、以降に書いたような感じでもよいのでしょうか・・・？？意味が違ってたらほんとうにすみません。

（※今気が付きましたが、テーブル定義で間違って「売上」を「金額」というフィールド名にしてやってしまいました。「金額」は「売上」に読み替えてください。すみません。）


(01)Accessの場合
（テーブルは「Sheet1」というテーブルを作って使用：金額合計のみ）

「金額だけ」の月ごとのクロス集計なら以下のような感じで・・・。

TRANSFORM Sum(金額)
SELECT 支店番号
FROM Sheet1
WHERE 日付 BETWEEN #2018/04/01# AND #2018/09/30#
GROUP BY 支店番号
PIVOT Month(日付) & '月の金額';



(02)ExcelのMicrosoftQueryの場合
（D:\1\1.xlsx の 「Sheet1」シートを使用：金額合計のみ）

こちらも「金額だけ」。

TRANSFORM Sum(金額)
SELECT 支店番号
FROM `D:\1\1.xlsx`.`Sheet1$` `Sheet1$`
WHERE 日付 BETWEEN #2018/04/01# AND #2018/09/30#
GROUP BY 支店番号
PIVOT Month(日付) & '月の金額';



(03)「金額も件数も同時に」、という場合なら、苦肉の策で以下のような感じですとか・・・（FROM句の副問い合わせの中のWHERE句は、長くなるので省略してます。）

添付した図のようになります。

私も調べてみたのですが、TRANSFORM はもしかしたら、「集計用（値）の列」としては「1つの列しか作れない」かもしれません。よくはわかりませんが・・・。

なので、より詳しく分かる方に「そもそも　TRANSFORM句で２つの集計列を作れますか？」というご質問もされるとよいかもしれません。

私には、(01)や(02)のようなクエリを、金額と件数の２つを作って、支店番号でリレーションさせることくらいしか思いつきませんでした。


以下の例は、1つの集計列（値の列）の中で、数量と金額の2つの計算をして、「／」でつないでいます。
データの無い月は何もしないと「／」が表示されてしまうので「IIf」関数で条件分岐処理しました。

【Accessの場合】

TRANSFORM IIf(Sum([T1.数量])<>0,Sum([T1.数量]*[T1.金額]) & '／' & Sum([T1.数量]),'')
SELECT T1.支店番号
FROM (SELECT Sheet1.支店番号, Month(Sheet1.日付) & '月の金額と個数' AS [月] , Sheet1.日付, Sheet1.金額, 1 AS [数量] FROM Sheet1) AS T1
GROUP BY T1.支店番号
PIVOT T1.月;

【ExcelのMicrosoftQueryの場合】

TRANSFORM IIF(SUM([T1.数量])<>0 ,SUM([T1.数量]*[T1.金額]) & '／' & SUM([T1.数量]) ,'')
SELECT T1.支店番号
FROM (SELECT 支店番号, Month(日付) & '月の金額と個数' AS [月] , 日付, 金額, 1 AS [数量] FROM `D:\1\1.xlsx`.`Sheet1$` `Sheet1$`) AS T1
GROUP BY T1.支店番号
PIVOT T1.月

No.1 回答者： is125is125 回答日時： 2019/05/02 14:01

クエリで表示は無理なのではないでしょうか。

レポートかフォームの見た目を整えて、サブレポートあるいはサブフォームを使えばクロス集計クエリと同じ様に見えると思いますよ。

この回答へのお礼

ありがとうございます

お礼日時：2019/05/02 20:37