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

Question

お世話になります。

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

項目　　　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年分の
体裁を整えた状態で貼り付けしたいところです。

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

f_a_007 · Accepted Answer

【補足の補足】

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

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

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

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

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

f_a_007 · Answer

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

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

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

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

f_a_007 · Answer

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

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

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

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

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

chie65535 · Answer

＞教えて頂いた方法だと、見出し(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に変更すれば良いです。

30246kiku · Answer

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

f_a_007 · Answer

補足:

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

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

f_a_007 · Answer

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

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

と書くこともできます。

f_a_007 · Answer

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

いえ、違います。

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

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

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

f_a_007 · Answer

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

chie65535 · Answer

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

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が入ります。

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

【補足の補足】

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

実際にエラーが発生しなきゃーOK！

＞教えて頂いた方法だと、見出し(yyyy年mm月)の箇所が固定になってしまうかと思います。

Excel に出力するという事なので、

補足:

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

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

クロス集計は、そもそも添付図のように表示されます。

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

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

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