Combine Data from Multiple Fields

[KHume]

Hello,

I have an Access database with 50+ fields with one field containing a name the remaining fields containg the score for a category. I want to consolidate the data into 3 Fields: Name, Category (contians the Field Heading name) and Score so it will be easier to report on. Does anyone have an idea for a quick way to do this?

Thank you,
Kevin Hume

 

[mdmilner]

Ok, I'm not testing this but what about using VBA:
Go ahead and create the new table first. There are a couple tweakable things still in this code, like using/not using variables to hold some values.

Mike

Public Function RedesignTable()
Dim dbs As DAO.Database
Dim rs,rs2 As DAO.Recordset
Dim tblname, strSQL, strName As String
Dim X As Integer

Set dbs = CurrentDB

tblName = "put name of your sourcetable here"
strSQL = "Select * from " & tblname
Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

tblName = "put name of your destination table here"
strSQL = "Select * from " & tblname
Set rs2 = dbs.OpenRecordset(strSQL, dbOpenDynaset)

With rs
  Do Until rs.EOF
    strName = .fields(0).Value
    With rs2
      For X = 1 to rs.fields.count -1
        .AddNew
        .fields(0).Value = strName
        .fields(1).Value = rs.fields(X).Name
        .fields(2).Value = rs.fields(X).Value
        .Update
      Next X
    End With
    .MoveNext
  Loop
End With

Set rs = Nothing
Set rs2 = Nothing
Set dbs = Nothing
End Function