Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


9880 / 13644 ツリー ←次へ | 前へ→

【24563】アクセスデータの集計 さつき 05/4/26(火) 15:29 質問[未読]
【24570】Re:アクセスデータの集計 小僧 05/4/26(火) 19:42 回答[未読]
【24582】Re:アクセスデータの集計 さつき 05/4/27(水) 13:55 お礼[未読]
【24675】Re:アクセスデータの集計 さつき 05/5/2(月) 15:29 質問[未読]
【24676】Re:アクセスデータの集計 m2m10 05/5/2(月) 16:42 発言[未読]
【24721】Re:アクセスデータの集計 さつき 05/5/6(金) 10:51 お礼[未読]
【24771】Re:アクセスデータの集計 さつき 05/5/9(月) 18:59 質問[未読]
【24776】Re:アクセスデータの集計 小僧 05/5/10(火) 9:31 回答[未読]
【24780】Re:アクセスデータの集計 さつき 05/5/10(火) 11:19 質問[未読]
【24787】Re:アクセスデータの集計 小僧 05/5/10(火) 16:15 回答[未読]
【24868】Re:アクセスデータの集計 さつき 05/5/12(木) 9:43 お礼[未読]
【24879】Re:アクセスデータの集計 小僧 05/5/12(木) 13:26 回答[未読]
【24897】Re:アクセスデータの集計 さつき 05/5/12(木) 20:14 お礼[未読]
【24903】Re:アクセスデータの集計 小僧 05/5/13(金) 10:19 回答[未読]
【24974】Re:アクセスデータの集計 さつき 05/5/16(月) 15:06 お礼[未読]
【24573】Re:アクセスデータの集計 kazu 05/4/26(火) 21:02 発言[未読]
【24583】Re:アクセスデータの集計 さつき 05/4/27(水) 13:56 お礼[未読]
【24576】Re:アクセスデータの集計 ichinose 05/4/26(火) 23:43 発言[未読]
【24584】Re:アクセスデータの集計 さつき 05/4/27(水) 13:58 お礼[未読]

【24563】アクセスデータの集計
質問  さつき  - 05/4/26(火) 15:29 -

引用なし
パスワード
   VBA初心者です。初めて投稿させて頂きます。
以下のような集計を行いたいのですが、どのようにしたらいいでしょうか?

店名  契約者名  品名 
A店  佐藤    IBM
B店  神田    NEC
C店  広田    FUJI
A店  佐藤    NEC
A店  中川    FUJI
C店  広田    SHARP
B店  神田    IBM
A店  中川    SONY
B店  大村    SHARP
とあったとして、集計結果には、契約者ごとの購入数の数値だけでなく、それぞれの品名もセルに一覧表示しなくてはなりません。(今はその部分のコードは考え中です。)

A店 佐藤 IBM、NEC
   <件数> 2 
   中川 FUJI、SONY
   <件数> 2
   計   4
B店 神田 NEC、IBM
   <件数> 2
   大村 SHARP
   <件数> 1
   計   3
C店 広田 FUJI、SHARP
   <件数> 2
   計   2

下記のようにすると、店名の横に印刷される集計データは
ひとつ前の店の数字になってしまいます。
そこで、
Cells(n, 1) = rst.Fields("品名").Value 
の=以降のところを
= hinban 
などとすると、こんどは値は正しいのですが、「null値の使い方が不正です」と出ます。
どうしたらいいでしょうか?

Sub TEST3()
  ' 参照設定「Microsoft Active Data Object 2.x Library」
  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim hinmei As String
  Dim tenmei As String
  Dim keiyakusha As String
  Dim c As Integer
  Dim n As Integer
  Dim t As Integer
    
  Set cnn = New ADODB.Connection
  cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Documents and Settings\週報.mdb"
  cnn.Open
  Set rst = New Recordset
  
  rst.Open "累積", cnn
  
  hinmei = ""
  tenmei = ""
  keiyakusha = ""


  rst.MoveFirst
  
Do Until rst.EOF = True

  c = c + 1
  
  If keiyakusha <> rst.Fields("契約者名").Value Then
    n = n + 1
    ' 行の変数を加算し必要項目を選択してセルにセット
       Cells(n, 1) = rst.Fields("品名").Value
       Cells(n, 2) = rst.Fields("店名").Value
       Cells(n, 3) = rst.Fields("契約者名").Value
       Cells(n, 4) = c
    t = t + 1

    hinmei = rst.Fields("品名").Value
    tenmei = rst.Fields("店名").Value
    keiyakusha = rst.Fields("契約者名").Value

    c = 0
  End If

  rst.MoveNext

Loop

  rst.Close
  Set rst = Nothing
  cnn.Close
  Set cnn = Nothing

End Sub

【24570】Re:アクセスデータの集計
回答  小僧  - 05/4/26(火) 19:42 -

引用なし
パスワード
   ▼さつき さん:
こんばんは。

あまり上手なコードではありませんが、
参考になれば幸いです。

Sub 集計()
Dim SQLCode As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim dbName As String
Dim Kiten As Range
Dim Cnt As Long
  
  dbName = "C:週報.mdb"
  Set Kiten = Range("A1")
  SQLCode = "SELECT 累計.店名, 累計.契約者名, " _
      & "Count(累計.品名) AS 品名のカウント " _
      & "FROM 累計 " _
      & "GROUP BY 累計.店名, 累計.契約者名;"
  
  Set cnn = New ADODB.Connection
  cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
             & "Data Source=" & dbName
  cnn.Open
  
  Set rst = New Recordset
  rst.Open SQLCode, cnn
  Range("A1").CopyFromRecordset rst
  rst.Close

  For Cnt = Kiten.Row To Kiten.End(xlDown).Row
    SQLCode = "SELECT 累計.品名 " _
        & "FROM 累計 " _
        & "WHERE 店名 = '" & Cells(Cnt, 1) _
        & "' AND 契約者名 = '" & Cells(Cnt, 2) & "'"
    
    rst.Open SQLCode, cnn
      Do Until rst.EOF
        Cells(Cnt, 4) = Cells(Cnt, 4) & " " & rst![品名]
        rst.MoveNext
      Loop
    rst.Close
  Next
  
  Set rst = Nothing
  cnn.Close
  Set cnn = Nothing
End Sub

【24573】Re:アクセスデータの集計
発言  kazu  - 05/4/26(火) 21:02 -

引用なし
パスワード
   ▼さつき さん:


>Cells(n, 1) = rst.Fields("品名").Value 
>の=以降のところを = hinban 
>などとすると、こんどは値は正しいのですが、「null値の使い方が不正です」と出ます。
↑はString 型の変数にNullを格納しようとした為起こったものです。
型定義をしなければこのエラーは出ないと思いますよ。

SQL変更しないでマクロで処理してみました。


Type TANTOUCHK
  Tantou1 As Variant
  Hinmoku() As Variant
End Type

Type Zokusei
  Tenmei As Variant
  Tantou() As TANTOUCHK
End Type


Sub TEST3()

  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset

  Dim Tenpo() As Zokusei
  ReDim Tenpo(0)

  Set cnn = New ADODB.Connection
  cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Documents and Settings\999393\My Documents\週報.mdb"
  cnn.Open
  Set rst = New Recordset

  rst.Open "累積", cnn
  rst.MoveFirst

  Do Until rst.EOF
    Chk = False
    BufCnt = UBound(Tenpo)
    For I = 0 To BufCnt
      If Tenpo(I).Tenmei = rst.Fields("店名") Then
        IntBuf = I
         Chk = True
      End If
    Next
  
    If BufCnt = 0 And Tenpo(0).Tenmei = "" Then
      BufCnt = 0
    Else
      BufCnt = BufCnt + 1
    End If
  
  
    If Not Chk Then
      ReDim Preserve Tenpo(BufCnt)
      Tenpo(BufCnt).Tenmei = rst.Fields("店名")
      ReDim Tenpo(BufCnt).Tantou(0)
      IntBuf = BufCnt
    End If
  
    Chk = False
    BufCnt = UBound(Tenpo(IntBuf).Tantou)
    For I = 0 To BufCnt
      If Tenpo(IntBuf).Tantou(I).Tantou1 = rst.Fields("契約者名") Then
        IntBuf2 = I
        Chk = True
      End If
    Next

    If BufCnt = 0 And Tenpo(IntBuf).Tantou(0).Tantou1 = "" Then
      BufCnt = 0
    Else
      BufCnt = BufCnt + 1
    End If

    If Not Chk Then
      ReDim Preserve Tenpo(IntBuf).Tantou(BufCnt)
      Tenpo(IntBuf).Tantou(BufCnt).Tantou1 = rst.Fields("契約者名")
      ReDim Tenpo(IntBuf).Tantou(BufCnt).Hinmoku(0)
      IntBuf2 = BufCnt
    End If
  
    Bg = UBound(Tenpo(IntBuf).Tantou(IntBuf2).Hinmoku)
    If Bg = 0 And Tenpo(IntBuf).Tantou(IntBuf2).Hinmoku(0) = "" Then
      Bg = 0
    Else
      Bg = Bg + 1
    End If

    ReDim Preserve Tenpo(IntBuf).Tantou(IntBuf2).Hinmoku(Bg)
  
    Tenpo(IntBuf).Tantou(IntBuf2).Hinmoku(Bg) = rst.Fields("品名")
  
    rst.MoveNext
  Loop

RowInd = 1
  For I = 0 To UBound(Tenpo)
    CtALL = 0
    Cells(RowInd, 1) = Tenpo(I).Tenmei
    For J = 0 To UBound(Tenpo(I).Tantou)
      Cells(RowInd, 2) = Tenpo(I).Tantou(J).Tantou1
      Ct = UBound(Tenpo(I).Tantou(J).Hinmoku)
      CtALL = CtALL + Ct + 1
      For K = 0 To Ct
        Cells(RowInd, 3 + K) = Tenpo(I).Tantou(J).Hinmoku(K)
      Next
      RowInd = RowInd + 1
      Cells(RowInd, 2) = "<件数>"
      Cells(RowInd, 3) = Ct + 1
      RowInd = RowInd + 1
    Next
    Cells(RowInd, 2) = "計"
    Cells(RowInd, 3) = CtALL
    RowInd = RowInd + 1
  Next
End Sub

【24576】Re:アクセスデータの集計
発言  ichinose  - 05/4/26(火) 23:43 -

引用なし
パスワード
   ▼さつき さん、皆さん、こんばんは。
私もSQLを使用した方が簡単かな?と思いますが・・・。
それはともかく・・・。

>VBA初心者です。初めて投稿させて頂きます。
>以下のような集計を行いたいのですが、どのようにしたらいいでしょうか?
>
>店名  契約者名  品名 
>A店  佐藤    IBM
>B店  神田    NEC
>C店  広田    FUJI
>A店  佐藤    NEC
>A店  中川    FUJI
>C店  広田    SHARP
>B店  神田    IBM
>A店  中川    SONY
>B店  大村    SHARP
>とあったとして、集計結果には、契約者ごとの購入数の数値だけでなく、それぞれの品名もセルに一覧表示しなくてはなりません。(今はその部分のコードは考え中です。)
>
>A店 佐藤 IBM、NEC
>   <件数> 2 
>   中川 FUJI、SONY
>   <件数> 2
>   計   4
>B店 神田 NEC、IBM
>   <件数> 2
>   大村 SHARP
>   <件数> 1
>   計   3
>C店 広田 FUJI、SHARP
>   <件数> 2
>   計   2
>
>下記のようにすると、店名の横に印刷される集計データは
>ひとつ前の店の数字になってしまいます。
>そこで、
>Cells(n, 1) = rst.Fields("品名").Value 
>の=以降のところを
>= hinban 
>などとすると、こんどは値は正しいのですが、「null値の使い方が不正です」と出ます。
>どうしたらいいでしょうか?
↑この件ですが・・・。

別の媒体からアクセスのテーブルにデータをコピーしたものを運用すると
この手の現象が良く起こりますが・・・。

Null値とは、
「バリアント型 (Variant) に有効なデータが入っていないことを示す値」

となっています。hinbanという変数をVariant型にしてもエラーは起きませんが、
通常は、代替データに置き換えますけどね。
AccessだとNZ関数がありますよね?

私は、
    hinmei = nz(rst.Fields("品名").Value, "")

'======================
Function nz(chkdata, cnvdata)
  If IsNull(chkdata) Then
   nz = cnvdata
  Else
   nz = chkdata
   End If
End Function

なんてよく使いますが。
又は、Sql
select iif(isnull(品名),"""",品名) as 品名・・・・
なんて方法もあると思いますが・・・。

検討してみて下さい。

【24582】Re:アクセスデータの集計
お礼  さつき  - 05/4/27(水) 13:55 -

引用なし
パスワード
   ありがとうございます。
試してみます。

【24583】Re:アクセスデータの集計
お礼  さつき  - 05/4/27(水) 13:56 -

引用なし
パスワード
   ありがとうございます。
初心者ですので、理解できるかどうか不安ですが、がんばって勉強してみます。

【24584】Re:アクセスデータの集計
お礼  さつき  - 05/4/27(水) 13:58 -

引用なし
パスワード
   教えていただいてありがとうございます。
そういう理由だったのですね。
詳しく勉強してみて、また質問するかも知れませんが、よろしくお願いいたします。

【24675】Re:アクセスデータの集計
質問  さつき  - 05/5/2(月) 15:29 -

引用なし
パスワード
   店名  契約者名 1週目  2週目   3週目  4週目  品名
A店    佐藤    2        1    2   IBM NEC
A店    中川    1    1           SONY FUJI
計          3    1    1    2    ←
B店    神田    1    1    1    1   IBM NEC
B店    大村        1    1       SHARP
計          1    2    2    1    ←
C店    広田    1    1        2   FUJI SHARP
C店    野中            1       SONY
計          1    1    1    2    ←


以前教えて頂いた方法を参考にさせて頂いて、下記のようなクロス集計クエリーを作っているのですが、店名ごとに合計行を追加することはできるでしょうか

  SQLCode = "TRANSFORM Count(品名) AS 品名のカウント " _
      & "SELECT 店名,契約者名 FROM 累計 " _
      & "GROUP BY 店名, 契約者名 " _
      & "PIVOT 週;"

【24676】Re:アクセスデータの集計
発言  m2m10  - 05/5/2(月) 16:42 -

引用なし
パスワード
   SQL では無理と思いますよ。

【24721】Re:アクセスデータの集計
お礼  さつき  - 05/5/6(金) 10:51 -

引用なし
パスワード
   ありがとうございます。

【24771】Re:アクセスデータの集計
質問  さつき  - 05/5/9(月) 18:59 -

引用なし
パスワード
   再びお伺いします。

>  For Cnt = Kiten.Row To Kiten.End(xlDown).Row
>    SQLCode = "SELECT 累計.品名 " _
>        & "FROM 累計 " _
>        & "WHERE 店名 = '" & Cells(Cnt, 1) _
>        & "' AND 契約者名 = '" & Cells(Cnt, 2) & "'"


例えば上の条件設定にもうひとつ追加したい場合、例えば下記のようにするとエラーがでるのですが、条件3つは不可能なのでしょうか?

        & "FROM 累計 " _
        & "WHERE 店名 = '" & Cells(Cnt, 1) _
        & "' AND 契約者名 = '" & Cells(Cnt, 2) & "'"
        & "' AND 担当者名 = '" & Cells(Cnt, 3) & "'"

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

【24776】Re:アクセスデータの集計
回答  小僧  - 05/5/10(火) 9:31 -

引用なし
パスワード
   ▼さつき さん:
おはようございます。

>例えば上の条件設定にもうひとつ追加したい場合、例えば下記のようにするとエラーがでるのですが、条件3つは不可能なのでしょうか?
>
>         & "FROM 累計 " _
>         & "WHERE 店名 = '" & Cells(Cnt, 1) _
>         & "' AND 契約者名 = '" & Cells(Cnt, 2) & "'"
→修正     & "' AND 契約者名 = '" & Cells(Cnt, 2) & "'" _

>         & "' AND 担当者名 = '" & Cells(Cnt, 3) & "'"
>
>よろしくお願いいたします。

ご提示された内容ですとSELECT文等、詳細な情報がないので
検証ができませんが…。

現在のままですと

   SELECT 累計.品名
   FROM 累計
   WHERE 店名 = 'A店' AND 契約者名 = '佐藤'' AND 担当者名 = '山田'

のように、不必要な「'」が入ってしまってますね。

【24780】Re:アクセスデータの集計
質問  さつき  - 05/5/10(火) 11:19 -

引用なし
パスワード
   できました。ありがとうございました。
おかげさまで、希望どおりになってきて、ほんとうに助かります。

もうひとつご質問させて頂いてもよろしいでしょうか?
下記のようなSQLを書きました。
  Set Kiten = Range("b1")
  SQLCode = "TRANSFORM Count(品名) AS 品名のカウント " _
      & "SELECT 店名,担当者名,契約者名 FROM 累積 " _
      & "GROUP BY 店名, 担当者名, 契約者名 " _
      & "PIVOT 週;"

以前教えて頂いた下記の部分を応用して、
  Set rst = New Recordset
  rst.Open SQLCode, cnn
  Range("A1").CopyFromRecordset rst
  rst.Close

店ごとの合計件数を表示させるように下記のコードを作りました。
s1,s2,s3には週ごとの値を合計させているつもりなのですが、合計数値がでません。
クロス集計の値は1件ずつ合計できないのでしょうか?
後、週によってはS4、S5と続く場合にあらかじめS4、S5をコードに入れておくと値が
ないときにエラーが出てしまいます。

  rst.Open SQLCode, cnn
  r = 1
  s1 = 0
  s2 = 0
  s3 = 0
  Do Until rst.EOF = True
      c = 0
      If tenmei <> rst.Fields(1).Value Then
          r = r + 1
          Cells(r, 4).Value = s1
          Cells(r, 5).Value = s2
          Cells(r, 6).Value = s3
          s1 = 0
          s2 = 0
          s3 = 0
      End If
      r = r + 1
      For Each fld In rst.Fields
        If Not IsNull(fld.Value) Then
          c = c + 1
          Cells(r, c).Value = fld.Value
        End If
      Next
          s1 = s1 + rst.Fields(3).Value
          s2 = s2 + rst.Fields(4).Value
          s3 = s3 + rst.Fields(5).Value
          tenmei = rst.Fields(1).Value
      rst.MoveNext
  Loop
  rst.Close

どうぞよろしくお願いいたします。

【24787】Re:アクセスデータの集計
回答  小僧  - 05/5/10(火) 16:15 -

引用なし
パスワード
   ▼さつき さん:
こんにちは。

かなり強引な処理になりますが、
SQLを多用して表の作成までのコードを作成してみました。

ACEESS側の [T_累計]の [週] フィールドは「S1」「S2」とテキスト型の
データが入っているフィールドと仮定して以下のコードを作りました。

Sub 集計2()
Dim CreCode As String
Dim DelCode As String
Dim In1Code As String
Dim In2Code As String
Dim SelCode As String
Dim DrpCode As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim dbName As String
Dim Kiten As Range
Dim Cnt As Long
Dim FLG As Boolean
Dim F As Variant
  
  dbName = "C:\週報.mdb"
    
  Call SQL作成(CreCode, DelCode, In1Code, _
             In2Code, SelCode, DrpCode)
    
  cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
             & "Data Source=" & dbName
  cnn.Open
  
  Call 削除処理(cnn, DelCode, CreCode)
    
  cnn.Execute In1Code
  cnn.Execute In2Code
    
  rst.Open SelCode, cnn

  For Cnt = 0 To rst.Fields.Count - 1
    Range("A1").Offset(0, Cnt) = rst(Cnt).Name
  Next
  
  Range("A2").CopyFromRecordset rst
  rst.Close


'-------------------------------------------------
'
'      H列に商品を並べる処理
'
'-------------------------------------------------

  cnn.Execute DrpCode
 
  Set rst = Nothing
  cnn.Close
  Set cnn = Nothing
End Sub

Function SQL作成(Cre As String, Del As String, In1 As String, _
          In2 As String, Sel As String, Drp As String)
    
Cre = "CREATE TABLE T_Temp(店名 text(50), 契約者名 text(50), " _
    & "担当者名 text(50), 週 text(50),品名のカウント int)"

Del = "DELETE * FROM T_Temp;"

In1 = "INSERT INTO T_Temp ( 店名, 担当者名, 契約者名, " _
                & "週, 品名のカウント ) " _
  & "SELECT 累計.店名, 累計.担当者名, 累計.契約者名, 累計.週, " _
  & "Count(累計.品名) AS 品名のカウント " _
  & "FROM 累計 " _
  & "GROUP BY 累計.店名, 累計.担当者名, 累計.契約者名, 累計.週;"

In2 = "INSERT INTO T_Temp ( 店名, 週, 担当者名, 契約者名, " _
                   & "品名のカウント ) " _
  & "SELECT 累計.店名 & 'の合計' AS 店名, 累計.週, '' AS 担当者名, " _
  & "'' AS 契約者名, Count(累計.品名) AS 品名のカウント " _
  & "FROM 累計 " _
  & "GROUP BY 累計.店名 & 'の合計', 累計.週, '', '';"

Sel = "TRANSFORM Sum(T_Temp.品名のカウント) AS 品名の合計 " _
  & "SELECT T_Temp.店名, T_Temp.担当者名, T_Temp.契約者名 " _
  & "FROM T_Temp " _
  & "GROUP BY T_Temp.店名, T_Temp.担当者名, T_Temp.契約者名 " _
  & "PIVOT T_Temp.週;"

Drp = "DROP TABLE T_temp"

End Function

Function 削除処理(cnn As Object, DelCode As String, CreCode)
On Error Resume Next
  cnn.Execute DelCode
    If Err.Number <> 0 Then
      cnn.Execute CreCode
    End If
  On Error GoTo 0
End Function

ACCESS側にワークテーブルを作って店ごとの合計件数をそのまま
一気にエクセルに貼り付けるコードです。

さつきさんがこちらのコードで宜しいようでしたら、
引き続き商品を並べる処理を作ろうと思うのですがいかがでしょうか?

【24868】Re:アクセスデータの集計
お礼  さつき  - 05/5/12(木) 9:43 -

引用なし
パスワード
   ありがとうございます。
いろいろお世話をかけっぱなしですいません。
とても長いコードなので、把握するのに時間がかかりそうです。
以前教えて頂いたエクセル上の検索条件を読み込む方法がとても気に入っていたのですが、その方法は応用できないのでしょうか?クロス集計の数値データを1件ずつ読み込むことはやはり不可能なのでしょうか?
もしそうでしたら、続きの商品を並べるコードをぜひ教えて下さい。

【24879】Re:アクセスデータの集計
回答  小僧  - 05/5/12(木) 13:26 -

引用なし
パスワード
   ▼さつき さん:
こんにちは。
何とかして集計に合計を加えたSQLを一括でできないか色々悩んでいたのですが、
こんな感じでいかがでしょうか?

Sub 集計3()
Dim SQLCode As String
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim dbName As String
Dim Kiten As Range
Dim Cnt As Long
Dim Num As Long
Dim Ten As String
Dim Kei As String
Dim Syu As String
Dim Tan As String
  
'DBの設定
  dbName = "C:週報.mdb"

'起点の設定
  Set Kiten = Range("B2")
  
'集計クエリのSQL文を作成
  SQLCode = "SELECT Q.* " _
      & "FROM [ " _
      & "TRANSFORM Count(累計.品名) AS 品名のカウント " _
      & "SELECT 累計.店名, 累計.契約者名, 累計.担当者名 " _
      & "FROM 累計 " _
      & "GROUP BY 累計.店名, 累計.契約者名, 累計.担当者名 " _
      & "PIVOT 累計.週 " _
      & "IN ('S1','S2','S3','S4','S5') " _
      & "]. AS Q " _
      & "UNION ALL SELECT T.* " _
      & "FROM [ " _
      & "TRANSFORM Count(累計.品名) AS 品名のカウント " _
      & "SELECT 累計.店名, '' AS 契約者名, '合計' AS 担当者名 " _
      & "FROM 累計 " _
      & "GROUP BY 累計.店名, '', '合計' " _
      & "PIVOT 累計.週 " _
      & "IN ('S1','S2','S3','S4','S5') " _
      & "]. AS T " _
      & "ORDER BY 店名 ,契約者名 DESC;"

'ADOでSQLを実行
  cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
             & "Data Source=" & dbName
  cnn.Open
  
'下行のコメントを外すとイミディエトウィンドウにSQLを表示
  'Debug.Print SQLCode
  rst.Open SQLCode, cnn

'フィールド名を起点の列に挿入
  For Cnt = 0 To rst.Fields.Count - 1
    Kiten.Offset(0, Cnt).Value = rst(Cnt).Name
  Next
    Kiten.Offset(0, rst.Fields.Count).Value = "品名"
  
'データの貼り付け
  Kiten.Offset(1, 0).CopyFromRecordset rst
  rst.Close

'店名のフィールドが空になるまで
  For Cnt = Kiten.Row + 1 To Kiten.End(xlDown).Row
    
'「合計」の行はスキップ
    If Cells(Cnt, Kiten.Column + 2) <> "合計" Then

'「S1」〜「S5」までをループ
      For Num = Kiten.Column + 3 To Kiten.Column + 7
        
'値が入っているフィールドが見つかったら変数「Syu」にフィールド名を入力
        If Not (IsEmpty(Cells(Cnt, Num).Value)) Then
          Syu = Cells(Kiten.Row, Num).Value: Exit For
        End If
      Next

      SQLCode = "SELECT 累計.品名 " _
          & "FROM 累計 " _
          & "WHERE 店名 = '" & Cells(Cnt, Kiten.Column).Value & "' AND " _
             & "契約者名 = '" & Cells(Cnt, Kiten.Column + 1).Value & "' AND " _
             & "担当者名 = '" & Cells(Cnt, Kiten.Column + 2) & "' AND " _
             & "週 = '" & Syu & "'"

'下行のコメントを外すとイミディエトウィンドウにSQLを表示
      'Debug.Print SQLCode
      rst.Open SQLCode, cnn
      
'品名がある限りループして「品名」フィールドに追加
      Do Until rst.EOF
        Cells(Cnt, Kiten.Column + 8) = Cells(Cnt, Kiten.Column + 8) & " " & rst![品名]
        rst.MoveNext
      Loop
    rst.Close
    End If
  Next
  
'オブジェクトの解放
  Set rst = Nothing
  cnn.Close
  Set cnn = Nothing
  
  Kiten.Offset(0, 8).Columns.EntireColumn.AutoFit
  Set Kiten = Nothing
End Sub

累計テーブルの「週」フィールドが文字型の「S1」,「S2」というような値であれば
うまくいくと思います。

【24897】Re:アクセスデータの集計
お礼  さつき  - 05/5/12(木) 20:14 -

引用なし
パスワード
   小僧さん、ありがとうございます。
大変お手数をかけて申し訳ありません。
これまた長い文ですので、把握するのに時間がかかります。
わからないところがあったら、またどうぞよろしくお願いいたします。
S1,S2・・・はクロス集計した数値なのですが、それでも大丈夫でしょうか?
IN・・・の使い方を初めて見ましたので、理解できていません。すいません。

【24903】Re:アクセスデータの集計
回答  小僧  - 05/5/13(金) 10:19 -

引用なし
パスワード
   ▼さつき さん:
おはようございます。

累計テーブルが

>店名  契約者名  担当者名  品名    週
>A店    佐藤    中田    IBM     S1
>B店    佐藤    中田    NEC    S1
>C店    広田    中田    FUJI    S2
>A店    佐藤    中村    NEC    S2
>A店    中川    中村    FUJI    S2
>C店    広田    高原    SHARP  S3
>C店    神田    中村    IBM    S3
>A店    中川    高原    SONY   S4
>B店    大村    中田    SHARP  S4
>A店    佐藤    中田    NEC    S1

こんな感じだとして、
>>TRANSFORM Count(累計.品名) AS 品名のカウント
>>SELECT 累計.店名, 累計.契約者名, 累計.担当者名
>>FROM 累計
>>GROUP BY 累計.店名, 累計.契約者名, 累計.担当者名
>>PIVOT 累計.週;

というSQLを発行すると
>店名  契約者名  担当者名  S1  S2   S3   S4
>A店    佐藤    中村        1        
>A店    佐藤    中田    2            
>A店    中川    高原                1
>A店    中川    中村        1        
>B店    佐藤    中田    1            
>B店    大村    中田                1
>C店    広田    高原            1    
>C店    広田    中田        1        
>C店    神田    中村            1

というような結果が返ってきます。
ところが「S5」というフィールドがないので、強制的にIN句で
フィールドを指定します。

>>TRANSFORM Count(累計.品名) AS 品名のカウント
>>SELECT 累計.店名, 累計.契約者名, 累計.担当者名
>>FROM 累計
>>GROUP BY 累計.店名, 累計.契約者名, 累計.担当者名
>>PIVOT 累計.週
>>IN ("S1","S2","S3","S4","S5")

>店名  契約者名  担当者名  S1  S2   S3   S4   S5
>A店    佐藤    中村        1        
>A店    佐藤    中田    2            
>A店    中川    高原                1
>A店    中川    中村        1        
>B店    佐藤    中田    1            
>B店    大村    中田                1
>C店    広田    高原            1    
>C店    広田    中田        1        
>C店    神田    中村            1

IN句のイメージとしてはこんな感じです。     

【24974】Re:アクセスデータの集計
お礼  さつき  - 05/5/16(月) 15:06 -

引用なし
パスワード
   小僧 さん
ありがとうございます。お礼が遅れてすいません。
じっくり勉強してみます。またわからないことがあったらよろしくお願いいたします。
いつもお世話をおかけして申し訳ありませんが、ほんとうに勉強になり、助かります。

9880 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free