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