Transpose with repeated data

[Guest]

Hi All,
I've seen all the answers to the transposing questions, but mine is a little
different. I actually want to do the reverse, I would like to take the row
data and convert to columns:

Currently:
PupilIDX, Set1,Set2,Set3,Set4
PupilIDY, Set1,Set2,Set3,Set4

I have over 800 rows to convert, doing this quickly would be highly
beneficial.

Many thanks for all your help, in advance

Wendy


Need:
PupilIDX, Set1
PupilIDX, Set2
PupilIDX, Set3
PupilIDX, Set4
PupilIDY, Set1
PupilIDY, Set2 etc.

I have
The PupilID needs to be repeated on each row for each pupil enrolled in a
class.

 

[Duane Hookom]

I think you could use a union query:

SELECT PupilID, Set1 as TheValue, 1 as TheNum
FROM tblNoNameGiven
UNION ALL
SELECT PupilID, Set2, 2
FROM tblNoNameGiven
UNION ALL
SELECT PupilID, Set3, 3
FROM tblNoNameGiven
UNION ALL
SELECT PupilID, Set4, 4
FROM tblNoNameGiven;

 

[Guest]

Duane, you are a star...that works really well, thank you.