高ポイント差し上げます。T1,T2テーブルがあります。T1にはitem_code,item_nameがあります。T2にはitem_code,code1,code_name,code_flgとあります。

T1.item_codeは重複がありません。T2.item_codeは重複があります。つまりT1.item_code='001','002'... T2.item_codeには重複があります。'001','001','001'といった具合にです。
例)T1.item_code=001,002,003...
T2.item_code=001,001,001,002,002,002....
T2.code1=C1,C2,C3,C1,C2,C3 (T2.item_codeの001にC1,C2,C3がついています。)
T2.code_name=ABC,DEF,GHI,ABC,DEF,GHI(T2.item_codeの001にABC,DEF,GHIがついています。)
T2.code_flg=1,1,1,1,0,1(T2.item_codeの001に1,1,1がついています。)

つまりT2のテーブルは、
001,C1,ABC,1
001,C2,DEF,1
001,C3,GHI,1
002,C1,ABC,1
...
といった具合になっています。この値をSQLで引っ張ってきて、Excelに出力をする時に、item_code,item_name,ABC,DEF,GHI(見出し)
001,アイテムコード1,1,1,1
002,アイテムコード2,1,0,1
といった具合に出力したいと思っています。つまりitem_codeの重複は取り除きます。
T2.code_nameをフィールド名にします。ABC,DEF,GHIの各列の下にはcode_flgの値を出力したいと思っています。これを実現するにはどのようなコードを書けばよいでしょうか?

回答の条件
  • 1人2回まで
  • 登録:2009/03/24 10:32:15
  • 終了:2009/03/26 06:44:38

ベストアンサー

id:Mook No.1

Mook回答回数1312ベストアンサー獲得回数3912009/03/24 13:00:30

ポイント400pt

あるカラム(フィールド)のデータを1行に表示したいというのはよく聞く要望ですが、

本来のデータベースの働きからするとあまり簡単な処理ではありません。


MySQL ではこれを行うための GROUP_CONCAT という関数を提供していますが、他の DB では

このような機能はないので、Access でやる場合も VBAを使い自分で表形式を制御する必要があります。


一応ご希望の仕様にしたつもりですが、異なる点があったらコメントください。

Option Compare Database

Sub exportExcel()
'// 出力用の EXCEL の設定
    Dim objXL As Object
    Set objXL = CreateObject("Excel.Application")
    objXL.Application.Visible = True
    
    Dim objWB As Object
    Set objWB = objXL.Workbooks.Add()
    
    Dim objWS As Object
    Set objWS = objWB.Worksheets(1)
    
    Dim DB As Database
    Set DB = CurrentDb
    
'// 集計用のクエリ
    Dim sql As String
    sql = "SELECT T1.item_code AS item_code, item_name, code_name, code1, code_flg " _
        & "FROM T2, T1 WHERE T2.item_code = T1.item_code " _
        & "ORDER BY T2.item_code, code_name"

    Dim RS As Recordset
    Set RS = DB.OpenRecordset(sql)
  
    Dim res As String
    Dim i As Integer
    
'// code_name 管理用に Dictionary を使用
    Dim objDic As Object
    Set objDic = CreateObject("Scripting.Dictionary")
    
    Dim colNum As Long
    colNum = 3
    
'// タイトル行の出力
    objWS.Cells(1, 1).Value = "item_code"
    objWS.Cells(1, 2).Value = "item_name"
    
    Dim r As Long, c As Long
    r = 2
'// 2行目のデータの設定
    objWS.Cells(r, 1).Value = RS!item_code
    objWS.Cells(r, 2).Value = RS!item_name
    
    Dim cKey As String, pKey As String
    pKey = RS!item_code & "/" & RS!item_name
    
    Do Until RS.EOF
        cKey = RS!item_code & "/" & RS!item_name
'// グルーピング処理
        If cKey <> pKey Then
            pKey = cKey
            r = r + 1
            objWS.Cells(r, 1).Value = RS!item_code
            objWS.Cells(r, 2).Value = RS!item_name
        End If

'// code_name の列の計算
        If objDic.Exists(CStr(RS!code_name)) Then
            c = objDic.Item(CStr(RS!code_name))
        Else
            objDic.Add CStr(RS!code_name), colNum
            c = colNum
            objWS.Cells(1, c).Value = RS!code_name
            colNum = colNum + 1
        End If
'// code_name のデータの出力
        objWS.Cells(r, c).Value = RS!code_flg
        RS.MoveNext
    Loop

    RS.Close
    Set objDic = Nothing
    Set RS = Nothing
End Sub
id:akaired

ご回答ありがとうございます。見出しの出力は固定にしました。つまり、ここの部分の処理は必要なくなりました。1行目のitem_code,item_name,ABC,DEF,GHIの見出しは必要なくなりました。ただ、abcの下にcode_flg,DEFの下にcode_flg,GHIの下にcode_flgの値を必要とします。また出力を4行目からにしたい場合(2行目,3行目は空白)どのようにすればよいでしょうか?

2009/03/24 14:58:10

その他の回答(1件)

id:Mook No.1

Mook回答回数1312ベストアンサー獲得回数3912009/03/24 13:00:30ここでベストアンサー

ポイント400pt

あるカラム(フィールド)のデータを1行に表示したいというのはよく聞く要望ですが、

本来のデータベースの働きからするとあまり簡単な処理ではありません。


MySQL ではこれを行うための GROUP_CONCAT という関数を提供していますが、他の DB では

このような機能はないので、Access でやる場合も VBAを使い自分で表形式を制御する必要があります。


一応ご希望の仕様にしたつもりですが、異なる点があったらコメントください。

Option Compare Database

Sub exportExcel()
'// 出力用の EXCEL の設定
    Dim objXL As Object
    Set objXL = CreateObject("Excel.Application")
    objXL.Application.Visible = True
    
    Dim objWB As Object
    Set objWB = objXL.Workbooks.Add()
    
    Dim objWS As Object
    Set objWS = objWB.Worksheets(1)
    
    Dim DB As Database
    Set DB = CurrentDb
    
'// 集計用のクエリ
    Dim sql As String
    sql = "SELECT T1.item_code AS item_code, item_name, code_name, code1, code_flg " _
        & "FROM T2, T1 WHERE T2.item_code = T1.item_code " _
        & "ORDER BY T2.item_code, code_name"

    Dim RS As Recordset
    Set RS = DB.OpenRecordset(sql)
  
    Dim res As String
    Dim i As Integer
    
'// code_name 管理用に Dictionary を使用
    Dim objDic As Object
    Set objDic = CreateObject("Scripting.Dictionary")
    
    Dim colNum As Long
    colNum = 3
    
'// タイトル行の出力
    objWS.Cells(1, 1).Value = "item_code"
    objWS.Cells(1, 2).Value = "item_name"
    
    Dim r As Long, c As Long
    r = 2
'// 2行目のデータの設定
    objWS.Cells(r, 1).Value = RS!item_code
    objWS.Cells(r, 2).Value = RS!item_name
    
    Dim cKey As String, pKey As String
    pKey = RS!item_code & "/" & RS!item_name
    
    Do Until RS.EOF
        cKey = RS!item_code & "/" & RS!item_name
'// グルーピング処理
        If cKey <> pKey Then
            pKey = cKey
            r = r + 1
            objWS.Cells(r, 1).Value = RS!item_code
            objWS.Cells(r, 2).Value = RS!item_name
        End If

'// code_name の列の計算
        If objDic.Exists(CStr(RS!code_name)) Then
            c = objDic.Item(CStr(RS!code_name))
        Else
            objDic.Add CStr(RS!code_name), colNum
            c = colNum
            objWS.Cells(1, c).Value = RS!code_name
            colNum = colNum + 1
        End If
'// code_name のデータの出力
        objWS.Cells(r, c).Value = RS!code_flg
        RS.MoveNext
    Loop

    RS.Close
    Set objDic = Nothing
    Set RS = Nothing
End Sub
id:akaired

ご回答ありがとうございます。見出しの出力は固定にしました。つまり、ここの部分の処理は必要なくなりました。1行目のitem_code,item_name,ABC,DEF,GHIの見出しは必要なくなりました。ただ、abcの下にcode_flg,DEFの下にcode_flg,GHIの下にcode_flgの値を必要とします。また出力を4行目からにしたい場合(2行目,3行目は空白)どのようにすればよいでしょうか?

2009/03/24 14:58:10
id:memo77 No.2

memo77回答回数238ベストアンサー獲得回数202009/03/24 13:47:34

ポイント100pt

code_flgの値域によるんですが、こんなSQLでも取れますよ。

SELECT
 T2SUM.item_code
,T1.item_name
,T2SUM.ABC
,T2SUM.DEF
,T2SUM.GHI
FROM
(SELECT
 item_code
,Max(IIF(code_name="ABC",code_flg,0)) AS ABC
,Max(IIF(code_name="DEF",code_flg,0)) AS DEF
,Max(IIF(code_name="GHI",code_flg,0)) AS GHI
FROM T2
GROUP BY item_code
) AS T2SUM
INNER JOIN T1
ON T2SUM.item_code=T1.item_code
  • id:akaired
    ACCESSになります。
  • id:akaired
    ご回答ありがとうございます。見出しの出力は固定にしました。つまり、ここの部分の処理は必要なくなりました。1行目のitem_code,item_name,ABC,DEF,GHIの見出しは必要なくなりました。ただ、abcの下にcode_flg,DEFの下にcode_flg,GHIの下にcode_flgの値を必要とします。また出力を4行目からにしたい場合(2行目,3行目は空白)どのようにすればよいでしょうか?
  • id:Mook
    code_flg の部分はともかく出力先を変更るだけなら、
    r=2 の部分を r=4 にするだけです。
  • id:akaired
    Dim cKey As String, pKey As String以下が理解できませんので、よろしかったら解説願えないでしょうか。申し訳ございません。また見出しは今回利用しないので(固定にするので)objDicの部分は全て省けばよいのでしょうか?
  • id:Mook
    item_code + item_name を ループ中記憶しておいて、これが変わったタイミングで記入列を1行増やす
    ことで同じ item_code を1行にまとめるという処理をしています。


    code_name に関しては、クエリの結果を1行ずつ削除するのですが、code_name の列に合わせてデータを
    書く処理をしているので、現在の処理を削除することはできません。

    タイトル行がないと どの code_name がどの列かを特定できませんが、それは構わないのですか。
    であれば、単純に処理後にセルを空にすればよいのだと思いますが。

    本当に code_name が3列しかないのであれば、一番最後に
    objWS.Cells(1, 3).Value = ""
    objWS.Cells(1, 4).Value = ""
    objWS.Cells(1, 5).Value = ""
    で、タイトル行が空になります。

  • id:Mook
    意味のわからない文章ですみません。下記訂正です。
    誤:code_name に関しては、クエリの結果を1行ずつ削除するのですが、code_name の列に合わせてデータを
    正:code_name に関しては、クエリの結果を1行ずつ処理するので、code_name の列に合わせてデータを


    クエリや処理で工夫するより、テーブル設計を見直した方がよさそうな気もしますが、
    code_name が固定であるなら、memo77 さんのクエリでやる方がよさそうな気もします。
    それであれば、途中以降は
        :
        :
      Dim r As Long
      r = 4
      Do Until RS.EOF
        For i=0 To 4
          objWS.Cells(r, i+1).Value = RS.Fields(i)
        Next
        r = r + 1
        RS.MoveNext
      Loop

      RS.Close
      Set RS = Nothing
    End Sub

    だけですみます。
  • id:akaired
    Mookさん。ご回答ありがとうございます。理解できました!あと1つ教えてください。
    RSのレコードをカウントしたいと思います。rs.recordcountとすればレコード件数を取得することができるのは理解しています。ただこれだとitem_codeが重複しているので、つまり以下のようになっています。
    001
    001
    001
    002
    002
    ...
    これを教えて頂いたコードで書くとexcel出力時、下のようにitem_codeの重複がなくなります。この重複なしのレコード数をカウントしたいのですが、どのようにレコードをカウントすればよいでしょうか?
    001
    002
    003

    Dim x as Long
    x=1
    Do Until RS.EOF
    ・・・
       RS.MoveNext
    x=x+1
    Loop
    上記のようにxをカウンターにしても当然ながら無理です・・・
  • id:Mook
    ポイントといるか賞ありがとうございました。


    EXCEL でのレコード数ということは行数のことですよね?
    r が行を指す変数ですから、初期値が4であるなら

     r=4
     Do
      :
      r = r+1
      :
     Loop
     MsgBox "EXCELのデータ行数は" & r - 4

    ということになると思いますが、それでよろしいでしょうか。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません