Turning many columns into 2

[jenp]

Every year, we receive data about what exams our students will be taking. This comes in a standard format that can't be altered until after we receive it:

student_id  1    2     3     4    5    6     7    ...     40
1234X       1          3     4         6           
2234X            2           4    5    6     7            40

Nearly all students take 5 or 6 exams, so nearly all students have 5 or 6 exam numbers spreas throughout any of the 40 fields.

Because almost all students take a set number of exams, it's easy enough to change this into:

student_id  ex1   ex2   ex3   ex4  ex5   ex6
1234X       1     3     4      6   28a    
2234X       2     4     5      6   7      40

Each exam gets three marks - 2 interim and one agreed, so I think what I'd like to do is import the above format into an Access table (n.b. I have simplified the table here based on the problem I'm trying to solve - it has a lot more data we need in other capacities) and then create a new table that looks like this:

student_id  exam   mark_1   mark_2   final_mark
1234X        1
1234X        3
...   
2234X        2
2234X        4
etc...

The only way I can see to do this (at the moment) is by running 6 separate append queries to take each exam column and append it to the new table, and I'm sure that can't be right. There must be a more efficient way.

Anyone got any ideas?

Cheers

-Jen

 

[jenp]

You know what... forget it. I don't even know why I bother to try to get help here. I said in my original message that I've given you the bare minimum of what I'm trying to do in order to spare everyone the gory details, but obviously you didn't get that far before you decided I hadn't learned anything in the two years since my first post here.

For the record: Regulations stipulate that a student CANNOT take more than 6 exams and also stipulate the number of marks that must be given for each script. These have been standard for over a hundred years and aren't likely to change.

So, I'll just go ahead with my six separate queries. At least it'll get me where I need to be with a relatively minimum amount of effort. I can't afford to spend hours modifying the data file sent to us by the board of exams every year, and neither can the secretaries who deal with student data. The solution I've come up with takes 5 minutes of work. Seems a good trade off to me for a bit of bad normalisation in the throwaway table.

 

[twoplustwo]

wtf? I thought Pat's solution was best. I can only envisage you having bigger issues down the line. A bit of pain now could potentially save a *lot* of pain (maybe a rewrite) later.

gl

 

[jenp]

Telling me to read up on normalisation isn't a solution.

Even if I do go with one mark per row I still would like to know how to GET them that way without making people spend hours creating the tables by hand for import.