【Ａｃｃｅｓｓで困っています】クロス集計の列見出し

質問

お世話になります。

クロス集計後の列見出しの並び替えですが
2009下期，2009上期，2008年下期・・・・・と
下期、上期と並べたいのですが、上期，下期と並んでしまいます。
クエリプロパティで設定すればいいのですが、たくさんあるので、【関数】かＳＱＬ文でのやり方をご存知の方教えて下さい。よろしくお願いします。

回答No.1

30246kiku · ベストアンサー

回答がないようですが、解決されましたでしょうか。

このクエリはどこに使うのでしょうか。

ただ単にクエリを表示するだけであれば、表示後、列を移動して保存すれば以降その状態で見ることができるようです。
但し、このクエリを使ってExcelにエクスポートしたりするのなら、列の並びが元の状態になるようです。

> クエリプロパティで設定すればいいのですが
「クエリ列見出し」での設定と思います。

ここで設定した並び順で、列が表示されるようです。
（Excelへのエクスポートでも有効みたい）
（クエリをSQLビューで見た時、PIVOT xxx In (yyyy) の yyyy 部分になります）

そこで、抽出期間を指定してクエリを作り直すVBA処理を作りこみます。

確認用環境）

テーブル「T9」

以下サンプル　（an はオートナンバ）
an　　　場所　　日付　　　 　　数量
1　　　AAA　　2009/04/01　　　94　　　　　
2　　　AAA　　2009/03/01　　　93　　　　　
3　　　BBB　　2009/04/01　　　94　　　　　
4　　　BBB　　2009/03/01　　　93　　　　　
5　　　AAA　　2010/03/01　　　103　　　　　
6　　　AAA　　2010/04/01　　　104　　　　　

クエリ「Q9」

TRANSFORM Sum(T9.数量) AS 数量の合計
SELECT T9.場所
FROM T9
GROUP BY T9.場所
PIVOT Year(DateAdd("m",-3,T9.日付)) & IIf(Month(DateAdd("m",-3,T9.日付))<=6,"上期","下期");

クエリの表示結果

場所　　2008下期　　2009下期　　2009上期　　2010上期
AAA　　　　93　　　　　　103　　　　　　94　　　　　　104
BBB　　　　93　　　　　（空白）　　　　 94　　　　　（空白）　

やりたいクエリは、

TRANSFORM Sum(T9.数量) AS 数量の合計
SELECT T9.場所
FROM T9
GROUP BY T9.場所
PIVOT Year(DateAdd("m",-3,T9.日付)) & IIf(Month(DateAdd("m",-3,T9.日付))<=6,"上期","下期") In ('2010上期','2009下期','2009上期','2008下期');

の結果は、

場所　　2010上期　　2009下期　　2009上期　　2008下期
AAA　　　　104　　　　　　103　　　　　94　　　　　　93
BBB　　　（空白）　　　 （空白）　　　 94　　　　　　93

クエリ「Q9」との違いは、PIVOT の最後に、In (yyyy) があることだけ。

年と上期/下期を指定して作りこもうとしていますが、どうせならクエリの抽出条件に日付の範囲を指定できるようにしておきます。
基本的なクエリ「Q9_B」を作成しておいて、そのクエリの内容を書き換えたものを「Q9」に設定し直す方法としてみます。
「Q9_B」の内容は置き換えが楽なように作っておきます。
（クエリ「Q9」は存在する前提とします）

基本クエリ「Q9_B」の内容

TRANSFORM Sum(T9.数量) AS 数量の合計
SELECT T9.場所
FROM T9
WHERE T9.日付 BETWEEN #XXXX# AND #YYYY#
GROUP BY T9.場所
PIVOT Year(DateAdd("m",-3,T9.日付)) & IIf(Month(DateAdd("m",-3,T9.日付))<=6,"上期","下期") In (ZZZZ);

ここでの、XXXX/YYYY/ZZZZ を条件に合わせて書き換えたものを「Q9」に設定します。

設定用関数例）

Private Sub MakeBetweenQuery_Q9(dt_s As Date, dt_e As Date)
　　Dim wdt As Date
　　Dim sTmp As String
　　Dim sTmp2 As String
　　Dim sSql As String

　　wdt = dt_s
　　sTmp = ""
　　While (wdt <= dt_e)
　　　　If (Month(DateAdd("m", -3, wdt)) <= 6) Then
　　　　　　sTmp2 = "上期"
　　　　Else
　　　　　　sTmp2 = "下期"
　　　　End If
　　　　sTmp = ",'" & Year(DateAdd("m", -3, wdt)) & sTmp2 & "'" & sTmp
　　　　wdt = DateAdd("m", 6, wdt)
　　Wend

　　If (Month(DateAdd("m", -3, dt_e)) <= 6) Then
　　　　If (sTmp2 = "下期") Then
　　　　　　sTmp2 = "上期"
　　　　Else
　　　　　　sTmp2 = ""
　　　　End If
　　Else
　　　　If (sTmp2 = "上期") Then
　　　　　　sTmp2 = "下期"
　　　　Else
　　　　　　sTmp2 = ""
　　　　End If
　　End If
　　If (Len(sTmp2) > 0) Then
　　　　sTmp = ",'" & Year(DateAdd("m", -3, dt_e)) & sTmp2 & "'" & sTmp
　　End If

　　sSql = CurrentDb.QueryDefs("Q9_B").SQL
　　sSql = Replace(sSql, "XXXX", Format(dt_s, "yyyy/mm/dd"))
　　sSql = Replace(sSql, "YYYY", Format(dt_e, "yyyy/mm/dd"))
　　sSql = Replace(sSql, "ZZZZ", Mid(sTmp, 2))
　　CurrentDb.QueryDefs("Q9").SQL = sSql
End Sub

使用例）

抽出期間（日付）を指定する場合、

Private Sub Sample1()
　　Call MakeBetweenQuery_Q9(#10/1/2008#, #3/31/2010#)
End Sub

これを実行すると、クエリ「Q9」は以下の内容となります。

TRANSFORM Sum(T9.数量) AS 数量の合計
SELECT T9.場所
FROM T9
WHERE T9.日付 BETWEEN #2008/10/01# AND #2010/03/31#
GROUP BY T9.場所
PIVOT Year(DateAdd("m",-3,T9.日付)) & IIf(Month(DateAdd("m",-3,T9.日付))<=6,"上期","下期") In ('2009下期','2009上期','2008下期');

テーブル「T9」全てを対象とする場合、

Private Sub Sample2()
　　Call MakeBetweenQuery_Q9(DMin("日付", "T9"), DMax("日付", "T9"))
End Sub

これを実行すると、クエリ「Q9」は以下の内容となります。

TRANSFORM Sum(T9.数量) AS 数量の合計
SELECT T9.場所
FROM T9
WHERE T9.日付 BETWEEN #2009/03/01# AND #2010/04/01#
GROUP BY T9.場所
PIVOT Year(DateAdd("m",-3,T9.日付)) & IIf(Month(DateAdd("m",-3,T9.日付))<=6,"上期","下期") In ('2010上期','2009下期','2009上期','2008下期');

※　関数名は適宜変更してください

※　細かいところまで確認していませんので、おかしなところがあればドンドン修正してください。

※　こういうことでなかったら、スルーしてください。