troubleshooting Question

How do I normalise spreadsheet data with lots of  rows and columns?

Avatar of Finchie
Finchie asked on
Microsoft Access
13 Comments1 Solution1130 ViewsLast Modified:
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
1                  23
3            22

up to 300 pupils

My normalised data should have 3 fields
PupilID, Exam, Mark
Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros