Microsoft Access Club Access超初心者対象Forum Access初級者対象Forum Access VBA Tips Forum DAO、ADO、SQL Forum

     

リストへもどる

投稿記事の一括表示

タイトルクロス集計クエリをレポートに。
記事No42770
投稿日: 2017/01/25(Wed) 11:41
投稿者さくら
解決済: ON
OS:windows 7
Access Version:2000

クロス集計クエリを元にレポートを作成したいです。

T製品マスタ
・製品ID(主キー)
・製品名

T入出庫データ
・入出庫ID(主キー)
・入出庫日
・製品ID
・入庫数
・出庫数

F月別出庫量
・txt年(テキストボックス)
・txt月(テキストボックス)
・txt日(テキストボックス)

起点となる日を入力させて

・txt年月日:=IIf([Forms]![F月別出庫量]![txt日]<21,DateSerial([Forms]![F月別出庫量]![txt年],[Forms]![F月別出庫量]![txt月]-1,20),DateSerial([Forms]![F月別出庫量]![txt年],[Forms]![F月別出庫量]![txt月],20))
・txt年月日2:=IIf([Forms]![F月別出庫量]![txt日]<21,DateSerial([Forms]![F月別出庫量]![txt年],[Forms]![F月別出庫量]![txt月]-7,21),DateSerial([Forms]![F月別出庫量]![txt年],[Forms]![F月別出庫量]![txt月]-6,21))

自動入力


これを元に、月別の出庫量合計(20日締め・直近6か月分)のクロス集計クエリを作成

行見出し 製品名
列見出し 式1: Format(IIf(Day([入出庫日])>20,DateAdd("m",1,[入出庫日]),[入出庫日]),"yyyy\/mm""月度""")
値 出庫数の合計: Nz(Sum([出庫数]),0)
Where条件 入出庫日(抽出条件)Between [Forms]![F月別出庫量]![txt年月日] And [Forms]![F月別出庫量]![txt年月日2]

クエリ パラメータ
[Forms]![F月別出庫量]![txt年月日] 日付/時刻型
[Forms]![F月別出庫量]![txt年月日2] 日付/時刻型


これで、クロス集計クエリは問題なく表示されます。


次に↓これを参考にレポートを作成。
http://www.accessclub.jp/bbs/0238/beginers76128.html

フッター部分の合計は必要ないので、

レコードソース:クロス集計クエリを指定
ページヘッダー:「Label0」〜「Label5」
詳細:非連結のテキストボックス「Field0」〜「Field5」

レポートの「開く時」イベント
Private Sub Report_Open(Cancel As Integer)
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim fld As DAO.Field
Dim cnt As Integer

Set db = CurrentDb
Set qd = db.QueryDefs(Me.RecordSource)

For cnt = 0 To qd.Fields.Count - 1
Set fld = qd.Fields(cnt)
Me("Label" & cnt).Caption = fld.Name
Me("Field" & cnt).ControlSource = fld.Name
Next
End Sub

としたのですが、
レポートを開くと
「”を有効なフィールド名、または式として認識できません。」
のエラーが出ます。

クロス集計クエリは表示できているので
レポートで何かがダメなんだとは思うのですが、
どこが間違っているのかわかりません。

よろしくお願いいたします。

タイトルRe: クロス集計クエリをレポートに。
記事No42771
投稿日: 2017/01/26(Thu) 23:17
投稿者mayu
解決済: ON
こんばんは。

> どこが間違っているのかわかりません。

パラメータクエリの中で指定されている
フォーム名やコントロール名を解釈できるのは
Access.Application配下のオブジェクトが持つ機能なので
DAO や ADO には当てはまらないんです。

つまり、DAO や ADO でパラメータクエリを扱う際は
フォームが開いているかどうかに関係なく
定義されているパラメータへ値をセットする必要があります。

また、インタラクティブに実行するクロス集計クエリの
列数や列名といった属性は
実際にクエリを発行しなければ取得できません。

まとめると
-----------------------------------------------------------------
【 1 】 QueryDef.Parameters コレクション全てに 値を設定
【 2 】 QueryDef.OpenRecordset で Recordsetオブジェクトを生成
【 3 】 Recordset.Fields コレクションから 可変の列名を取得
-----------------------------------------------------------------
という手順を踏みます。

'---------------------------------------------------------------------------
Private Sub Report_Open(Cancel As Integer)
  Dim i As Long
  
  If (Not CurrentProject.AllForms("F月別出庫量").IsLoaded) Then
    Rem Cancel = True
    Rem Exit Sub
  End If
  
  With CurrentDb.QueryDefs(Me.RecordSource)
    .Parameters("[Forms]![F月別出庫量]![txt年月日]").Value = _
           Forms("F月別出庫量").Controls("txt年月日").Value

    .Parameters("[Forms]![F月別出庫量]![txt年月日2]").Value = _
           Forms("F月別出庫量").Controls("txt年月日2").Value
    
    With .OpenRecordset
      For i = 0 To .Fields.Count - 1
        'Debug.Print .Fields(i).Name
        Me("Label" & i).Caption = .Fields(i).Name
        Me("Field" & i).ControlSource = "[" & .Fields(i).Name & "]"
      Next i
      .Close
    End With
  End With
End Sub
'---------------------------------------------------------------------------


ただ、個人的には
フォーム名やコントロール名を、クエリに固定値で埋め込むのではなく
対象日時の範囲( 直近6ヶ月 )や締日( 20 )も
柔軟に変更できるようにしたいですね。

標準モジュールへ 以下のような関数を1つ登録しておいて
必要な時に呼び出して使うようにすればどうでしょうか。
クエリパラメータに該当する部分は、全て関数の引数で受け渡すようにします。
( 保存済クエリは一切使いません )

'-------------------------------------------------------------------------------------------------
'/*
' ***********************************************************
' *
' *  クロス集計のSQL文を生成します
' *
' *   引数1:  年
' *   引数2:  月
' *   引数3:  日
' *   引数4:  締日( 月末は 0 を指定 )
' *   引数5:  引数 1,2,3 で指定した年月日の直近Xヶ月
' *         ※ 遡る場合は負の数
' *         ※ 延ばす場合は正の数
' *
' ***********************************************************
'*/
Public Function get_sql(ByVal iYear    As Long _
           , ByVal iMonth   As Long _
           , ByVal iDay     As Long _
           , ByVal iClose   As Long _
           , ByVal iCompare As Long) As String
  Dim dtBase  As Date
  Dim dtTemp  As Date
  Dim dtStart As Date
  Dim dtEnd   As Date
  
  dtBase = DateSerial(iYear, iMonth, iDay)
  dtStart = DateSerial(Year(dtBase - iClose), Month(dtBase - iClose) + 2, 0)
  dtEnd = DateSerial(Year(dtBase - iClose), Month(dtBase - iClose) + 2 + iCompare, 0)
  
  If (dtStart > dtEnd) Then
    dtTemp = dtEnd
    dtEnd = dtStart
    dtStart = dtTemp
  End If
  
  get_sql = "TRANSFORM CLng( Nz( Sum( 出庫数 ), 0 ) ) "                           & vbNewLine _
      & "SELECT 製品名 "                                                      & vbNewLine _
      & "FROM       T製品マスタ   x "                                         & vbNewLine _
      & "INNER JOIN T入出庫データ y "                                         & vbNewLine _
      & "        ON x.製品ID = y.製品ID "                                     & vbNewLine _
      & "WHERE DateSerial( Year( 入出庫日 - " & CStr(iClose) & " ) "          & vbNewLine _
      & "                , Month( 入出庫日 - " & CStr(iClose) & " ) + 2 "     & vbNewLine _
      & "                , 0 "                                                & vbNewLine _
      & "      ) "                                                            & vbNewLine _
      & "          Between " & Format$(dtStart, "\#yyyy/mm/dd\#")             & vbNewLine _
      & "              And " & Format$(dtEnd, "\#yyyy/mm/dd\#")               & vbNewLine _
      & "GROUP BY 製品名 "                                                    & vbNewLine _
      & "ORDER BY 製品名 "                                                    & vbNewLine _
      & "PIVOT Format$( "                                                     & vbNewLine _
      & "          DateSerial( Year( 入出庫日 - " & CStr(iClose) & " ) "      & vbNewLine _
      & "                    , Month( 入出庫日 - " & CStr(iClose) & " ) + 2 " & vbNewLine _
      & "                    , 0 "                                            & vbNewLine _
      & "          ) "                                                        & vbNewLine _
      & "        , 'yyyy\/mm月度' "                                           & vbNewLine _
      & "      ) ;"
End Function
'-------------------------------------------------------------------------------------------------


'レポートのOpen時イベントはこんな感じ
'-----------------------------------------------------------------------
Private Sub Report_Open(Cancel As Integer)
  Const F_NAME As String = "F月別出庫量"
  Dim i        As Long
  Dim strSQL   As String
  
  If (Not CurrentProject.AllForms(F_NAME).IsLoaded) Then
    Rem Cancel = True
    Rem Exit Sub
  End If
  
  strSQL = get_sql(Forms(F_NAME).Controls("txt年").Value _
          , Forms(F_NAME).Controls("txt月").Value _
          , Forms(F_NAME).Controls("txt日").Value _
          , 20 _
          , -6)
  
  Me.RecordSource = strSQL
  
  With CurrentDb.CreateQueryDef("", strSQL)
    For i = 0 To .Fields.Count - 1
      'Debug.Print .Fields(i).Name
      Me("Label" & i).Caption = .Fields(i).Name
      Me("Field" & i).ControlSource = "[" & .Fields(i).Name & "]"
    Next i
  End With
End Sub
'-----------------------------------------------------------------------

  では ☆

タイトルRe: クロス集計クエリをレポートに。
記事No42772
投稿日: 2017/01/27(Fri) 14:58
投稿者hatena
解決済: ON
直接の回答ではなく、別案の提案です。
ご参考までに。

現状は、
列名が動的に変更するクロス集計クエリに対応刺させるために、VBAでコントロールソースやラベルの表題を変更していますが、
発想を変えて、列名を固定にしてしまう方法です。
下記にサンプルがあります。


列名が変化するクロス集計クエリと連結するフォーム - hatena chips
http://hatenachips.blog34.fc2.com/blog-entry-117.html


上記の例はフォームですが、レポートでも同様にできます。


現状の列見出しを下記に変更します。

式1: DateDiff("m",[Forms]![F月別出庫量]![txt年月日2]-20,[入出庫日]-20)


これで、列見出しが
0, 1, 2, 3, 4, 5
の固定になります。

「クエリ列見出し」プロパティを
0, 1, 2, 3, 4, 5
と設定します。

詳細のテキストボックスのコントロールソースもこの列名を設定します。

項目名のラベルはテキストボックスに変更して、コントロールソースを下記のように設定します。

=DateAdd("m",0,[Forms]![F月別出庫量]![txt年月日2])
=DateAdd("m",1,[Forms]![F月別出庫量]![txt年月日2])
=DateAdd("m",2,[Forms]![F月別出庫量]![txt年月日2])
・・・・

このテキストボックスの書式プロパティを

yyyy\/mm"月度"

と設定します。


以上です。
VBAも必要なく、パラメータがあっても問題ないです。

タイトルRe^2: クロス集計クエリをレポートに。
記事No42773
投稿日: 2017/01/30(Mon) 09:57
投稿者さくら
解決済: ON
>mayuさま
>hatenaさま

お返事が遅くなってしまい、申し訳ありません。
ご丁寧な回答、本当にありがとうございます。

mayuさまの1つ目、hatenaさまのご提案分、両方とも思う通りの結果が出ました。
まだまだ分かっていない部分も多く勉強不足でした。
Accessは、一つの答えを出すために本当に色々な方法があるんですね!

mayuさまの2つ目のご提案分も、運用面を考えれば
とても良い方法だと思うので是非使ってみたいところなのですが

「サブクエリに非固定列のクロス集計を使用することはできません」
のエラーが出てしまいました。
正直、SQL文生成のモジュールがあまり理解できておらず
(クエリ作成もデザインビューばかり使ってSQL文を理解してない部分が大きいです)
もうちょっと勉強してみます。
保存済みクエリを使わずにレポートって作れるんだ!
と、初心者丸出しでびっくりした私でした。

タイトルRe^3: クロス集計クエリをレポートに。
記事No42774
投稿日: 2017/01/30(Mon) 23:21
投稿者mayu
解決済: ON
解決したようで何よりでした。
本題は解決済みですので、以下参考までに。

> 「サブクエリに非固定列のクロス集計を使用することはできません」
> のエラーが出てしまいました。

私のミスです、ごめんなさい。(´;ω;`)

フォームやレポートのレコードソースに クロス集計クエリを指定する場合、
【 クエリ列見出し 】プロパティ
つまり、SQL文でのIN句 を固定する必要がありました。

'-------------------------------------------------------------------------------------------------
'/*
' ***********************************************************
' *
' *  クロス集計のSQL文を生成します
' *
' *   引数1:  年
' *   引数2:  月
' *   引数3:  日
' *   引数4:  締日( 月末は 0 を指定 )
' *   引数5:  引数 1,2,3 で指定した年月日の直近Xヶ月
' *         ※ 遡る場合は負の数
' *         ※ 延ばす場合は正の数
' *
' ***********************************************************
'*/
Public Function get_sql(ByVal iYear As Long _
           , ByVal iMonth As Long _
           , ByVal iDay As Long _
           , ByVal iClose As Long _
           , ByVal iCompare As Long) As String
  Dim dtBase  As Date
  Dim dtTemp  As Date
  Dim dtStart As Date
  Dim dtEnd   As Date
  Dim i       As Long
  ReDim sPvIn(Abs(iCompare)) As String
  
  dtBase = DateSerial(iYear, iMonth, iDay)
  dtStart = DateSerial(Year(dtBase - iClose), Month(dtBase - iClose) + 2, 0)
  dtEnd = DateSerial(Year(dtBase - iClose), Month(dtBase - iClose) + 2 + iCompare, 0)
  
  If (dtStart > dtEnd) Then
    dtTemp = dtEnd
    dtEnd = dtStart
    dtStart = dtTemp
  End If
  
  For i = 0 To Abs(iCompare)
    sPvIn(i) = Format$(DateSerial(Year(dtStart), Month(dtStart) + 1 + i, 0) _
             , "'yyyy\/mm月度'")
  Next i
  
  get_sql = "TRANSFORM CLng( Nz( Sum( y.出庫数 ), 0 ) ) "              & vbNewLine _
      & "SELECT x.製品名 "                           & vbNewLine _
      & "FROM        T製品マスタ   x "                     & vbNewLine _
      & "INNER JOIN  T入出庫データ y "                     & vbNewLine _
      & "        ON x.製品ID = y.製品ID "                    & vbNewLine _
      & "WHERE DateSerial( Year( y.入出庫日 - " & CStr(iClose) & " ) "     & vbNewLine _
      & "                , Month( y.入出庫日 - " & CStr(iClose) & " ) + 2 "   & vbNewLine _
      & "                , 0 "                         & vbNewLine _
      & "      ) "                               & vbNewLine _
      & "          Between " & Format$(dtStart, "\#yyyy/mm/dd\#")        & vbNewLine _
      & "              And " & Format$(dtEnd, "\#yyyy/mm/dd\#")         & vbNewLine _
      & "GROUP BY x.製品名 "                          & vbNewLine _
      & "ORDER BY x.製品名 "                          & vbNewLine _
      & "PIVOT Format$( "                            & vbNewLine _
      & "          DateSerial( Year( y.入出庫日 - " & CStr(iClose) & " ) "   & vbNewLine _
      & "                    , Month( y.入出庫日 - " & CStr(iClose) & " ) + 2 " & vbNewLine _
      & "                    , 0 "                       & vbNewLine _
      & "          ) "                             & vbNewLine _
      & "        , 'yyyy\/mm月度' "                       & vbNewLine _
      & "      ) "                               & vbNewLine _
      & "   IN ( " & Join(sPvIn, ",") & " ) ;"
End Function
'-------------------------------------------------------------------------------------------------


関数を標準モジュール上に登録した後、
VBE( Visual Basic Editor )上で Ctlキーを押しながら + Gキーを押し、
表示された イミディエイトウィンドウ上で

?get_sql(2017, 1, 22, 20, -6)

と引数を入力して、エンターキーを押すと
関数によって生成された SQL文 が表示されますので

クエリを新規作成し、TRANSFORM... 以下を
SQLビューに貼り付けてみると
どのようなデータが表示されるか 確認いただけると思います。
( 関数の結果を フォームやレポートの RecordSource にも指定できます )

  では ☆

タイトルRe^4: クロス集計クエリをレポートに。
記事No42776
投稿日: 2017/02/03(Fri) 09:05
投稿者さくら
解決済: ON
>mayuさま

レス通知メールが迷惑メールに振り分けられていて
気づくのが遅れました!
お返事くださっていたのに申し訳ありません。

教えてくださった式でばっちり動きました!!!
VBEでの確認の仕方も教えてくださって、ありがとうございます。

また一つ、Accessのことが少し分かりました。

でもまだまだ初心者。
コツコツ勉強を続けます。

本当にありがとうございます。

- 以下のフォームから自分の投稿記事を修正・削除することができます -
処理 記事No パスワード

ページの先頭へ 前ページへ戻る