Turn over the crosstab 1

[flaviooooo]

Hey,

I have a crosstab that's formatted like this:

A B C
Mat1 1 2 3
Mat2 6 7 8
...

Now I would like to get this like this:

Mat1 A 1
Mat1 B 2
Mat1 C 3
Mat2 A 6
Mat2 B 7
Mat2 C 8

Off course this is a simplified example, the real thing consists of 173 columns...

Thanks in advance for your assistance!

Kind regards
Fabian

 

[Wilciek]

I did a similar thing but I used VBA to do it. I've amended the code to fit yours. I should give you the feel for what I was doing. I've called your Crosstab xtab as if it were in spreadsheet format and your new table NewTable. I've named the fields Field_Matt, Column_Letter and Matt_Value.

There are probably other ways to do it but this worked for me. You may have to play about with the code as I converted it from my original application
-----------------------------------------------------------

Public Function fnSplitData()

Dim strMySQL As String
Dim MyRs_xtabAs Recordset
Dim MyRs_NewTable As Recordset
Dim ifld As Integer

Set MyRs_xtab= CurrentDb.OpenRecordset("xtab")

If MyRs_xtab.EOF Then
MsgBox " No data to process"
Exit Function
End If

MyRs_xtab.MoveFirst

While Not MyRs_xtab.EOF

For ifld = 1 To MyRs_xtab.Fields.Count - 1
strMySQL = "SELECT Field_Matt, Column_Letter, Matt_Value " & _
"FROM NewTable WHERE Field_Matt='" & MyRs_xtab!Field_Matt & "' " & _
"AND Column_Letter = '" & MyRs_xtab.Fields(ifld).Name & "'"

Set MyRs_NewTable = CurrentDb.OpenRecordset(strMySQL)

With MyRs_NewTable
If .EOF Then
.AddNew
!Field_Matt = MyRs_xtab!Field_Matt
!Column_Letter = MyRs_xtab!Column_Letter.Name
!Matt_Value = MyRs_xtab.Fields(ifld)
Else
.Edit
End If
!Matt_Value = MyRs_xtab.Fields(ifld)
.Update
End With
Next ifld
MyRs_xtab.MoveNext
Wend
End Function

 

[dhookom]

Didn't your crosstab begin with data in normalized tables?

However, you could try create a union query like

SELECT MatColumn, "A" as Alpha, [A] as TheValue
FROM unnamedCrosstab
UNION ALL
SELECT MatColumn, "B", [B]
FROM unnamedCrosstab
UNION ALL
SELECT MatColumn, "C", [C]
FROM unnamedCrosstab
UNION ALL
...

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Duane, the real thing consists of 173 columns
I'm pretty sure JetSQL chokes on an union of 173 select clauses ...

 

[dhookom]

PH,
I'm sure you are correct. The union method has limits and would probably need several similar union queries to append to a semi-permanent table. With potentially 173 columns, they probably aren't know at design time.

That's part of the reason why I asked:
"Didn't your crosstab begin with data in normalized tables?"

Duane
Hook'D on Access
MS Access MVP

 

[flaviooooo]

Unfortunately, it did not start with data in normalized tables (it was an excel file that was created in crosstab format from the get go)

Would it have been easier if it had?

Btw, that module of Wilciek did the trick (although it took a long time for the excessive data...)

Thanks for that!

 

[dhookom]

I thought by "crosstab" you meant crosstab query. A crosstab query might have started exactly like the end format you desire.

Duane
Hook'D on Access
MS Access MVP