Unanswered: ACCESS 2010 - Transpose and append fields to records via VBA created SQL query...
I have a table which is exported from an design program. The format is what we have to work with - not able to change, at least not at this time.
There are 50 columns, the first column is always the structure number, the succeeding columns are assemblies that go with that particular structure. The number of assemblies for each structure will vary, as will the assemblies.
We are trying to decompose this table to show what assemblies are included in each structure, and then create a BOM for the entire projects...but that part is already done.
I was planning on simply creating a really big SQL query, but I found some code on the web, and tried to implement a dynamic query that would not be fixed to columns names and so forth.
Option Compare Database
Dim rsMySet As DAO.Recordset
Dim strSQL As String
Dim OutputTable As String
Dim InputTable As String
Dim i As Integer
InputTable = "MatrixDataset"
OutputTable = "TabularDataset"
'Open the original matrix-style dataset
Set rsMySet = CurrentDb.OpenRecordset(InputTable)
'Start the count at the position number of the first column-oriented field
'Remember that Recordsets start at 0
For i = 1 To rsMySet.Fields.Count - 1
'Use the recordset field.name property to build out the SQL string for the current field
strSQL = "INSERT INTO TabularDataset ([StrNum],[Assembly]) " & _
"SELECT [MatrixDataset].[StructureNumber] as StrNum," & _
"'" & rsMySet.Fields(i).Value & "'" & " AS Assembly " & "FROM MatrixDataset WHERE " & _
"'" & rsMySet.Fields(i).Value & "'" & " <> '';"
'Execute the SQL string
'Move to the next column-oriented field
' Now we need to update the assembly to pull the quantity from
' the front of the field and place it in the Qty field
' UPDATE OutputTable SET Qty = left(Assy,instr(Assy,"-"-1))
strSQL = "UPDATE " & "`" & OutputTable & "` as ot" & " SET ot.Qty = left(ot.Assembly,instr(ot.Assembly,'-')-1);"
strSQL = "UPDATE " & "`" & OutputTable & "` as ot" & " SET ot.Assembly = right(ot.Assembly,len(ot.Assembly)-instr(ot.Assembly,'-'));"
'strSQL = "SELECT Assembly, Qty FROM `" & OutputTable & "` GROUP BY Assembly ORDER BY Assembly;"