「教えて!ピックアップ」リリース!

お世話になります。

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

A 回答 (1件)

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



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

ただ単にクエリを表示するだけであれば、表示後、列を移動して保存すれば以降その状態で見ることができるようです。
但し、このクエリを使って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下期');


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

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


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

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング