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

     

リストへもどる

新着の投稿記事


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

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

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

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

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

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

タイトルaccess2016を社内で共有する方法
記事No42775   [関連記事]
投稿日: 2017/01/31(Tue) 14:22
投稿者あいりん
OS:Win8
Access Version:2016

こんにちは。
いつもお世話になっています。
同じ質問を初級者向けの方でさせていただいたのですが、
宣伝のような書き込みが多く、ご回答をいただけなかったので、こちらで質問させていただきます。

access初心者ですが、会社の指示で顧客データベースをつくっています。
こちらで勉強させていただき、何とかデータベースが出来上がってきました。
そこで、今悩んでいるのが、社内でデータベースを共有する方法です。

ネットで調べてはみたのですが、昔の情報と新しい情報が錯綜していて、
一体どうすればいいのか余計わからなくなってきました。

会社の状況は、下記の通りです。

・社員10名ほど
・access2016がインストールされているのは、私のパソコン1台のみ
・他の社員のパソコンにはオフィス2013や2007、2003などそれぞれ違うバージョンのオフィスが入っている
・なるべく経費は使わずに共有したい(でもどうしても必要なら出せないこともない)
・サーバーは会社で所有・管理している

最初は、データをサーバーに置き、他の社員にaccess2016のランタイムを配ればいいと思ったのですが、みんなそれぞれ違うバージョンのオフィスが入っているので、難しいと思いました。

だったら、SharePointというものがいいのかと思ったのですが、ネットにデータを置くことになるので、セキュリティやの問題やランニングコストなどが気になります。

他にいい方法はないでしょうか?
実際に社内でデータベースを使用されている方や詳しい方がいらっしゃいましたら
アドバイスをいただけないでしょうか。

タイトル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^2: クロス集計クエリをレポートに。
記事No42773   [関連記事]
投稿日: 2017/01/30(Mon) 09:57
投稿者さくら
解決済: ON
>mayuさま
>hatenaさま

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

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

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

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

タイトル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: クロス集計クエリをレポートに。
記事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
'-----------------------------------------------------------------------

  では ☆

タイトルクロス集計クエリをレポートに。
記事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

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

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

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

タイトル複数列のクロス集計について
記事No42769   [関連記事]
投稿日: 2017/01/20(Fri) 15:41
投稿者さる
OS:win10
Access Version:2010

複数列のクロス集計について

データ
f1 f2 f3 f4 f5  f6
名前 あ     A      C
名前 い   B     

クロス集計 では

名前 あ い 

になりますが、

希望として
名前 あ い A B C

にしたいのですが。

この様な例で教えてください。
フィールド    f1    f2   f2   f2
テーブル     t     t    t    t
集計       グループ化      カウント カウント 
行列の入れ替え  行見出し  列見出し 値    行見出し

SQLビューではわかりずので
よろしくお願いいたします。



タイトルRe: 特定フィールドに含まれる語句を2つのコンボボックスで指定し、コマンドボタンを使ってレコードを抽出したい
記事No42768   [関連記事]
投稿日: 2017/01/20(Fri) 11:22
投稿者Genzo
Filterを上書きしているので、入社年コンボ2しか動いていないのでは
ないかと思います。

> ' フィルタの実行、変数を含む曖昧語を指定
> Me.Filter = "氏名 Like '*" & strSagasu1 & "*'"
> Me.Filter = "入社年 Like '*" & strSagasu2 & "*'" '<-ここで上書きしている

なので、

Me.Filter = "氏名 Like '*" & strSagasu1 & "*' and 入社年 Like '*" & strSagasu2 & "*'"

などとしてみてはいかがでしょうか?

タイトル特定フィールドに含まれる語句を2つのコンボボックスで指定し、コマンドボタンを使ってレコードを抽出したい
記事No42767   [関連記事]
投稿日: 2017/01/19(Thu) 17:02
投稿者できない
OS:Windows7
Access Version:2010

テーブル:T_社員情報 からフォームを作成しました。

T_社員情報
社員ID|氏名|入社年|出身地|
10102|田中| 1999|大阪|
10134|金子| 2014|長崎|

@コンボボックス1の名前:氏名コンボ1
Aコンボボックス2の名前:入社年コンボ2
Bコマンドボタンの名前:抽出

コマンドボタン:抽出のクリック時に下記イベントプロシージャを作成をしました。
Private Sub 抽出_Click()
' テキストボックス内容を保存する変数を定義
Dim strSagasu1 As String
Dim strSagasu2 As String
' テキストボックスの値を変数にいれる
strSagasu1 = Me![氏名コンボ1]
strSagasu2 = Me![入社年コンボ2]
' フィルタの実行、変数を含む曖昧語を指定
Me.Filter = "氏名 Like '*" & strSagasu1 & "*'"
Me.Filter = "入社年 Like '*" & strSagasu2 & "*'"
Me.FilterOn = True
End Sub
※一応、曖昧語でも抽出できるようにしてますが、曖昧語でなくても構いません。

実行結果は入社年コンボ2しか動いていないようです・・・(ノД`;)
どう修正したら2つのコンボボックスの条件から抽出できますか?

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