How do I normalise spreadsheet data with lots of rows and columns?
I have imported a spreadsheet from excel into Access that is not normalised. I wish to normalise the data (reverse cross-tab query or unpivot process). I am after a more efficient solution than Ive come across so far (eg typing each field title which is impractical). I am sure visual basic will provide the solution but Ive struggled.
My table is currently in the format below with PupilID in the first column and then exam marks in following columns.
PupilID Exam1 Exam2 Exam3 up to Exam300
up to 300 pupils
My normalised data should have 3 fields
PupilID, Exam, Mark
place this codes in a module
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim i As Integer, s, fldArr(), j
Set rs = CurrentDb.OpenRecordset("t
Set rs1 = CurrentDb.OpenRecordset("t
If rs.EOF Then
MsgBox "no records"
For i = 0 To rs.Fields.Count - 1
ReDim Preserve fldArr(i)
fldArr(i) = rs.Fields(i).Name
Do Until rs.EOF
For j = 2 To UBound(fldArr)
!pupilID = rs("pupilId")
!exam = rs.Fields(fldArr(j)).Name
!mark = rs.Fields(fldArr(j))