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.

Location of original code.

I had a partial success, where I was able to pull out the structure number and it's related assemblies, but it is simply repeating the assemblies from the first structure.

I'm still trying to see where I went wrong, but if anyone has any ideas as to a better approach, I'm open to any and all ideas.

Thank you for your time in this matter.

M.

MatrixDataset (input, partial)

Code:
StructureNumber	Structure  Comment  2	Structure  Comment  3	Structure  Comment  4	Structure  Comment  5	Structure  Comment  6	Structure  Comment  7	Structure  Comment  8	Structure  Comment  9
26-2	1-S80-H2	1-TS-15PG	1-TMF-CB	1-TM-124TDS	1-TM-9D(22)	1-TM-103	22-TG-1G	11-TG-21C
27	1-S90-H4*	1-TBP-161A	1-C9-3A(12)	1-TMF-4B	1-TM-9D(2)	1-TM-101	2-TG-1G	1-TG-21C
28	1-S90-H5*	1-TBP-161A	1-C9-3A(12)	1-TMF-4B	1-TM-9D	1-TM-101	2-OPT-D	1-TM-N
29	2-S105-H1*	1-TH-10PV4XX-SP	1-C9-3A(12)D	1-TMF-4B	1-TM-9F	2-TM-101	1-OPT-D	1-OPT-2D
30	3-S90-H2	1-TH-15PDX	1-TMF-112T	1-TM-9L	3-TM-101	1-OPT-D	1-OPT-2D	1-TM-N
TabularDataset (output, partial)

Code:
ID	StrNum	Assembly	Qty
22033	26-2	S80-H2	1.00
22067	26-2	TS-15PG	1.00
22101	26-2	TMF-CB	1.00
22135	26-2	TM-124TDS	1.00
22169	26-2	TM-9D(22)	1.00
22203	26-2	TM-103	1.00
22237	26-2	TG-1G	22.00
22271	26-2	TG-21C	11.00
22305	26-2	TA-5L	11.00
22339	26-2	OPT-D	1.00
22373	26-2	TM-N	1.00
22034	27	S80-H2	1.00
22068	27	TS-15PG	1.00
22102	27	TMF-CB	1.00
22136	27	TM-124TDS	1.00
22170	27	TM-9D(22)	1.00
22204	27	TM-103	1.00
22238	27	TG-1G	22.00
22272	27	TG-21C	11.00
22306	27	TA-5L	11.00
22340	27	OPT-D	1.00
22374	27	TM-N	1.00
VBA Code (sourced from the web)

Code:
Option Compare Database

Function TransposeTable()
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
CurrentDb.Execute strSQL

'Move to the next column-oriented field

Next i

' 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);"
CurrentDb.Execute strSQL

strSQL = "UPDATE " & "`" & OutputTable & "` as ot" & " SET ot.Assembly = right(ot.Assembly,len(ot.Assembly)-instr(ot.Assembly,'-'));"
CurrentDb.Execute strSQL

'strSQL = "SELECT Assembly, Qty FROM `" & OutputTable & "` GROUP BY Assembly ORDER BY Assembly;"
'CurrentDb.Execute strSQL

End Function