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行に表示したいというのはよく聞く要望ですが、
本来のデータベースの働きからするとあまり簡単な処理ではありません。
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
あるカラム(フィールド)のデータを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
ご回答ありがとうございます。見出しの出力は固定にしました。つまり、ここの部分の処理は必要なくなりました。1行目のitem_code,item_name,ABC,DEF,GHIの見出しは必要なくなりました。ただ、abcの下にcode_flg,DEFの下にcode_flg,GHIの下にcode_flgの値を必要とします。また出力を4行目からにしたい場合(2行目,3行目は空白)どのようにすればよいでしょうか?
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
ご回答ありがとうございます。見出しの出力は固定にしました。つまり、ここの部分の処理は必要なくなりました。1行目のitem_code,item_name,ABC,DEF,GHIの見出しは必要なくなりました。ただ、abcの下にcode_flg,DEFの下にcode_flg,GHIの下にcode_flgの値を必要とします。また出力を4行目からにしたい場合(2行目,3行目は空白)どのようにすればよいでしょうか?