Access クロス集計クエリについて

質問

お世話になります。

クロス集計クエリの結果が以下の内容になっているとします。

項目　　　2013年10月　　2014年07月　　2014年08月　　2014年09月
商品数　　100　　　　　　　　150　　　　　　　120　　　　　　　　130
顧客数　　2000　　　　　　　2200　　　　　　2100　　　　　　　2200
※2013年11月～2014年6月までのデータ無し

上記クエリで、データの無い期間の商品数及び顧客数を0とし、また見出しも
埋めたいところです。

項目　　　2013年10月　　2013年11月　・・・2014年08月　　2014年09月
商品数　　100　　　　　　　　0　　　　　　　　　120　　　　　　　　130
顧客数　　2000　　　　　　　0　　　　　　　　　2100　　　　　　　2200

このように表示したい場合、やはり元データにレコードを作成し各数量は0で
登録しなければなりませんでしょうか。
それとも、実際にレコードが無くても、上記表示は可能でしょうか。

************
当該クエリの内容は最終的にExcelファイルに貼り付けします。
現状、左詰めで貼り付けられてしまう為、可能であれば1年分の
体裁を整えた状態で貼り付けしたいところです。

ご教示の程、宜しくお願い致します。

回答No.7

山田 太郎（@f_a_007） · ベストアンサー

【補足の補足】

色々とテストした結果、列[年月]がテキスト型の場合には、In句が自動生成されませんね。日付型ですと、自動生成されるのですが・・・。

PIVOT [年月日] In ("2014年01月","2014年02月","2014年03月","2014年04月","2014年05月");

そして、テキスト型に限っては、このような書き方でも「型の不一致エラー」はでないようです。

In句を自動生成させるのか否か？

ちょっと、検討事項かもしれませんね。

お礼

たまたま？かもしれませんが、

midashi = "''2013年11月',　省略 ,'2014年10月'"　※
・・・
PIVOT Format([テーブル名].年月,"yyyy年mm月") In ( & midashi & );

で、うまくいきました。。本来ならエラーになるのでしょうか。

なお、※の箇所は開始年月からmidashiに入れる文字列を生成したいと
考えております

回答No.10

山田 太郎（@f_a_007）

多分、無意味な補足かと思いますが・・・。

1:TRANSFORM Sum(T1.件数) AS 件数のカウント
2:TRANSFORM Nz(Sum(T1.件数),0) AS 件数のカウント

1の戻り値は""
2の戻り値は0

ですから、空欄ではなく0を表示したい時は Nz()で。

回答No.9

山田 太郎（@f_a_007）

実際にエラーが発生しなきゃーOK！
私は、そう思います。
でも、仮にテキスト型だと意味がない整形です。
（添付図を参照のこと）

http://www.tsware.jp/tips/tips_025.htm

なお、質問者の解決策は、一般的なそれなようですね。私ならば、

PIVOT Format([テーブル名].年月,"yyyy年mm月") In (XXXXXX);

でクエリを用意しておいて、クエリの編集コマンドでXXXXXXを置換するでしょう。でも、midashi も良い手ですね。勉強になりました。

回答No.8

chie65536（@chie65535）

＞教えて頂いた方法だと、見出し(yyyy年mm月)の箇所が固定になってしまうかと思います。
＞例えば、今月11月に処理を実行する場合、前月(10月)から過去12ヵ月分で抽出しなければなりません。

開始年月は、ユーザーフォームか何かに入力して、クエリを実行するボタンか何かをクリックした時に結果が表示されるように作っていると思います。

なので、クエリを、ユーザーフォーム上のテキストボックスの値を参照して演算するようにすれば良いだけです。

例えば、「ユーザーフォーム」と言う名前のフォームに「開始年月」と言うテキストボックスを用意し、そこに「201309」のように年月を６桁の数字で入れさせるようにします。

SELECT 年月,SUM(商品数),SUM(顧客数)
FROM (SELECT 年月,商品数,顧客数 FROM 元テーブル
union
SELECT Format(DateAdd("m",0,Mid([Forms]![ユーザーフォーム]![開始年月],1,4) & "/" & Mid([Forms]![ユーザーフォーム]![開始年月],5,2) & "/01"),"yyyy年mm月") as 年月,0 as 商品数,0 as 顧客数 FROM 元テーブル
union
SELECT Format(DateAdd("m",1,Mid([Forms]![ユーザーフォーム]![開始年月],1,4) & "/" & Mid([Forms]![ユーザーフォーム]![開始年月],5,2) & "/01"),"yyyy年mm月") as 年月,0 as 商品数,0 as 顧客数 FROM 元テーブル
union
(中略)
union
SELECT Format(DateAdd("m",11,Mid([Forms]![ユーザーフォーム]![開始年月],1,4) & "/" & Mid([Forms]![ユーザーフォーム]![開始年月],5,2) & "/01"),"yyyy年mm月") as 年月,0 as 商品数,0 as 顧客数 FROM 元テーブル)
GROUP BY 年月

のようにします。

テキストボックスへの入力規則が「2013/09」のようになっているなら、クエリの中のMid関数の「開始文字位置」を5から6に変更すれば良いです。

お礼

chie65535様
度々のご回答ありがとうございます。
まだ実際に試しておりませんが、仰るとおりフォーム上に開始年月だけもたせて
そこを参照させるようにすればいけそうですね。

試してみたいと思います。
ありがとうございました。

回答No.6

30246kiku

Excel に出力するという事なので、
クロス集計結果を Excel に出力後、整形した方が簡単かと思います。

過去質問で解決したと思われる、見出し転記 ＆ CopyFromRecordset した範囲が
B4:F6 だった場合の一例です。

定数 xlShiftToRight がありますが、Access から使うのであれば
Const xlShiftToRight = -4161
と定義しておけばよいと思います。

以下は Excel で記述してみたら・・・というものになります。

Public Sub Samp1()
　　Dim iRows As Long, iCol As Long
　　Dim dtL As Date, dtC As Date
　　Dim i As Long, j As Long

　　With Range("B4:F6")
　　　　iRows = .Rows.Count
　　　　For iCol = .Columns.Count To 3 Step -1
　　　　　　With .Cells(1, iCol)
　　　　　　　　dtL = CDate(.Offset(, -1).Value)
　　　　　　　　dtC = CDate(.Value)
　　　　　　　　i = DateDiff("m", dtL, dtC) - 1
　　　　　　　　If (i > 0) Then
　　　　　　　　　　.Resize(iRows, i).Insert xlShiftToRight
　　　　　　　　　　For j = 1 To i
　　　　　　　　　　　　With .Offset(, -j)
　　　　　　　　　　　　　　.Resize(iRows).Value = 0
　　　　　　　　　　　　　　.Value = Format(DateAdd("m", -j, dtC), "yyyy年mm月")
　　　　　　　　　　　　End With
　　　　　　　　　　Next
　　　　　　　　End If
　　　　　　End With
　　　　Next
　　End With
End Sub

※ 単純にクロス集計時に盛り込もうとした時には
「列見出し」を設定すればデータの無い月の表示も可能です。
が、表示月の変更があれば修正する必要があります。
流動的に変更する方法もありますが・・・
それは、VBA でクロス集計のSQLを再定義するものになります。
ただし、データの無い部分の表示は空欄（空白）になります。

必要そうなら提示しますが、元テーブルの構成等補足してください。

なお、上記記述では、もし右端に罫線とかあった場合はズレて行きます。
そういった場合は、作業用シートに展開処理後、本シートに移すとか・・・

※ Access に記述するのなら、変数 MyRs がレコードセットと仮定すると
　　With Range("B4:F6")
部分の前に処理を追加します。
B4 から書き出すと仮定した雰囲気は（以下未検証）

　　Set MyRs = MyDB.OpenRecordset(MySQL)
　　With obj.Worksheets(シート名)
'　　　　.Activate
　　　　With .Range("B4")
　　　　　　For i = 0 To MyRs.Fields.Count - 1
　　　　　　　　.Offset(,i).Value = MyRs(i).Name
　　　　　　Next
　　　　　　.Offset(1).CopyFromRecordset MyRs
　　　　　　With .Resize(MyRs.RecordCount + 1, MyRs.Fields.Count)　' ★
　　　　　　　　' ★ 上記処理
　　　　　　End With
　　　　End With
　　End With

回答No.5

山田 太郎（@f_a_007）

補足:

PIVOT Format([年月日],"@@@@@@@@") In ("2014年01月","2014年02月","2014年03月","2014年04月","2014年05月");

列[年月]の型と表示のあり方では、このような書き方をしないと”型が一致しません！”というエラーも。この辺りは、試行錯誤されてください。

回答No.4

山田 太郎（@f_a_007）

"oooo"・・・・・これは、Accessの日付型の書式指定子の一つです。ですから、

PIVOT Format([年月日],"yyyy年mm月") In ("2014年01月","2014年02月","2014年03月","2014年04月","2014年05月");

と書くこともできます。

回答No.3

山田 太郎（@f_a_007）

＞これは元テーブルに9～12月のレコードが存在するからでしょうか？

いえ、違います。

PIVOT Format([年月日],"oooo") In (XXXXX);

XXXXX⇒"1月","3月","5月","11月","12月"

と書き換えると、表示は次のように。要は、 Format IN 句次第です。

お礼

f_a_007様
度々ありがとうございます。

今現在の年月からXXXXXの部分を生成すればよさそうですね。

ちなみにコチラの集計クエリのPIVOTの箇所は、
PIVOT [テーブル名].年月;
となっております。

これを教えて頂いたものに置き換えると、
PIVOT Format([テーブル名].年月,"oooo") In (XXXXX);
となりますが、"oooo"の箇所はどう記載すればよいのでしょうか。
なお、年月には「yyyy年mm月」というような文字列になってます。

回答No.2

山田 太郎（@f_a_007）

クロス集計は、そもそも添付図のように表示されます。データの無い月を非表示にするのが至難ですよ。

お礼

f_a_007様
いつもご回答ありがとうございます。

添付された画像は9～12月分の数量がブランクで、ちゃんと見出し
部分が表示されてますが、これは元テーブルに9～12月のレコードが
存在するからでしょうか？

質問させて頂いた例では2013年11月～2014年6月のレコードが
元テーブルにありません。

なので、この元テーブルを使って集計してもレコードが無い年月が
詰まって表示されます。
2013年10月　2014年07月　2014年08月　2014年09月

回答No.1

chie65536（@chie65535）

元テーブルをクロス集計しないで、

SELECT 年月,SUM(商品数),SUM(顧客数)
FROM (SELECT 年月,商品数,顧客数 FROM 元テーブル
union
SELECT "2013年10月" as 年月,0 as 商品数,0 as 顧客数 FROM 元テーブル
union
SELECT "2013年11月" as 年月,0 as 商品数,0 as 顧客数 FROM 元テーブル
union
SELECT "2013年12月" as 年月,0 as 商品数,0 as 顧客数 FROM 元テーブル
union
SELECT "2014年01月" as 年月,0 as 商品数,0 as 顧客数 FROM 元テーブル
union
SELECT "2014年02月" as 年月,0 as 商品数,0 as 顧客数 FROM 元テーブル
union
SELECT "2014年03月" as 年月,0 as 商品数,0 as 顧客数 FROM 元テーブル
union
SELECT "2014年04月" as 年月,0 as 商品数,0 as 顧客数 FROM 元テーブル
union
SELECT "2014年05月" as 年月,0 as 商品数,0 as 顧客数 FROM 元テーブル
union
SELECT "2014年06月" as 年月,0 as 商品数,0 as 顧客数 FROM 元テーブル
union
SELECT "2014年07月" as 年月,0 as 商品数,0 as 顧客数 FROM 元テーブル
union
SELECT "2014年08月" as 年月,0 as 商品数,0 as 顧客数 FROM 元テーブル
union
SELECT "2014年09月" as 年月,0 as 商品数,0 as 顧客数 FROM 元テーブル)
GROUP BY 年月

と言うクエリをクロス集計すれば、無い部分に0が入ります。

お礼

chie65535様
いつもご回答ありがとうございます。

教えて頂いた方法だと、見出し(yyyy年mm月)の箇所が固定に
なってしまうかと思います。
例えば、今月11月に処理を実行する場合、前月(10月)から過去12ヵ月分で
抽出しなければなりません。

説明不足で申し訳ございませんでした。

今回質問させて頂いたクロス集計クエリも、元テーブルをUNIONクエリにして
それをクロス集計しております。
※元テーブルが、yyyy年mm月、商品数、顧客数、拠点ID
　というように、商品数と顧客数が横並びになっている為。