Pivoting a table?

[RSW]

If I have a table with records like so (first number is ID)

1 A 1
2 B 3
3 C 7
4 A 2
5 B 9
6 C 2
7 A 1
8 B 8
9 C 4

Is there a way I can turn it into something like this (whether temporary and/or permanent) for better analysis?

1 A 1 2 1
2 B 3 9 8
3 C 7 2 4

Thanks in advance!

 

[the_net_2.0]

You may want to try the crosstab wizard. It will let you include more than one field as criteria for the records. In this case, I don't think you'll be able to include the 1, 2, 3's because that would be too complex for the wizard to do. That would essentially eliminate certain records and you cannot use a 'Where' clause for it because that cut out the other records you need.

Crosstabs have 3 evaluations to make though, and you only have 2 so it may not work here.

What kind of analysis are you considering doing? If it's something that is compatible with Access and its capabilities, outputting the data the way you have specified would not be the best option. What would be is to use a one-to-many relationship between the second and third columns and then query them out based on the appropriate analysis needs.

 

[Dairy Farmer]

Found this once on the net. Can't remember where. Never got to try it.

Function Transposer(strSource As String, strTarget As String)
Dim db As DAO.Database
Dim tdfNewDef As DAO.TableDef
Dim fldNewField As DAO.Field
Dim rstSource As DAO.Recordset, rstTarget As DAO.Recordset
Dim i As Integer, j As Integer
On Error GoTo Transposer_Err
Set db = CurrentDb()
Set rstSource = db.OpenRecordset(strSource)
rstSource.MoveLast
' Create a new table to hold the transposed data.
' Create a field for each record in the original table.
Set tdfNewDef = db.CreateTableDef(strTarget)
For i = 0 To rstSource.RecordCount
Set fldNewField = tdfNewDef.CreateField(CStr(i + 1), dbText)
tdfNewDef.Fields.Append fldNewField
Next i
db.TableDefs.Append tdfNewDef
' Open the new table and fill the first field with
' field names from the original table.
Set rstTarget = db.OpenRecordset(strTarget)
For i = 0 To rstSource.Fields.Count - 1
With rstTarget
.AddNew
.Fields(0) = rstSource.Fields(i).Name
.Update
End With
Next i
rstSource.MoveFirst
rstTarget.MoveFirst
' Fill each column of the new table
' with a record from the original table.
For j = 0 To rstSource.Fields.Count - 1
' Begin with the second field, because the first field
' already contains the field names.
For i = 1 To rstTarget.Fields.Count - 1
With rstTarget
.Edit
.Fields(i) = rstSource.Fields(j)
rstSource.MoveNext
.Update
End With
Next i
rstSource.MoveFirst
rstTarget.MoveNext
Next j
db.Close
Exit Function
Transposer_Err:
Select Case Err
Case 3010
MsgBox "The table " & strTarget & " already exists."
Case 3078
MsgBox "The table " & strSource & " doesn't exist."
Case Else
MsgBox CStr(Err) & " " & Err.Description
End Select
Exit Function
End Function