How to Normalize Existing Data

[bkf7911]

I need to create frequency reporting using existing data and wondered if there is a quick query/code that would allow this. I need to shift from current format below to the desired format below. Any suggestions?

Current Format
ID M1 M2 M3 M4
Alpha 1 2 3 4
Beta 4 3 2 1

Desired Format
ID Measure Score
Alpha M1 1
Alpha M2 2
Alpha M3 3
Alpha M4 4
Beta M1 4
Beta M2 3
Beta M3 2
Beta M4 1

 

[bkf7911]

ASIDE from selecting the data in the original table and then Unioning multiple select statements.

 

[MajP]

ASIDE from selecting the data in the original table and then Unioning multiple select statements.

Why??? The union query would be extremely simple.

 

[bkf7911]

I thought i'd complicate it. I can do the unions, but i'm interested in Code or query that can do this so it will be portable for future endeavors.

 

[MajP]

Assume that this did not go to M4 but instead went to M200. Then I would write code to do this. I would loop the records, and loop the fields and insert into the new table. Or you could write code to write the union sql.

 

[MajP]

Public Sub NormalizeMeasures()
  Dim rsUn As DAO.Recordset
  Dim rsNorm As DAO.Recordset
  Dim fld As DAO.Field
  Dim score As Integer
  Dim measure As String
  Dim id As String
  Dim strsql As String
  Set rsUn = CurrentDb.OpenRecordset("unnormal")
  
  Do While Not rsUn.EOF
    id = "'" & rsUn!id & "'"
    For Each fld In rsUn.Fields
      If Not fld.Name = "ID" Then
        measure = "'" & fld.Name & "'"
        score = fld.Value
        strsql = "Insert into tblNormal values (" & id & "," & measure & "," & score & ")"
        CurrentDb.Execute strsql
      End If
    Next fld
    rsUn.MoveNext
  Loop
End Sub

 

[SkipVought]

You could NORMALIZE your data in less than 5 minutes using this technique in Excel 2007+, assuming that your row count will not exceed 1.4 mil rows or Excel 2003 if you4 row sount will not exceed 65 k rows.

faq68-5287

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bkf7911]

Thanks for the updates! I'm going with MajP's code.... Appreciate the replies.