Looking for query to flip excel data over and over again. 2

[straybullet]

Been away awhile, working in finance now (not as much call for database design, until now!)

I've been given survey responses in Excel in the following format:
DataID recordid Q47_1 Q1 Q2 Q3 Q4
1 6347684356653 4356653 5 4 3 2
2 6347682756353 2756353 5 4 4 4
3 6347682233 2233 1 2 3 4
4 6347684356654 4356654 2 2 4 4
5 6347682756356 2756356 2 5 4 3
6 6347682231 2231 5 5 4 4
7 6347684356657 4356657 3 2 1 5
8 6347682756358 2756358 4 4 5 5
9 6347682239 2239 3 4 2 5

DataID is an autonumber field I added when bringing this data into Access.
Q1-Q4 represent individual questions with the response values below each for the 9 respondents (rows 1-9). I'd just manually copy and paste if I had to, but the above is the tip of the iceberg - there are 45 Q's across. What query could be run to turn the above into the below?

TDataID Q47_1 QNum Selection
1 4356653 Q1 5
2 4356653 Q2 4
3 4356653 Q3 3
4 4356653 Q4 2
55 2756353 Q1 5
56 2756353 Q2 4
57 2756353 Q3 4
58 2756353 Q4 4
109 2233 Q1 1
110 2233 Q2 2
111 2233 Q3 3
112 2233 Q4 4
163 4356654 Q1 2
164 4356654 Q2 2
165 4356654 Q3 4
166 4356654 Q4 4


Many thanks in advance!

Let them hate - so long as they fear... Lucius Accius

 

[PHV]

I"d use an UNION query

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I just used faq68-5287 today to take something similar and easily normalize it all in Excel.

Duane
Hook'D on Access
MS Access MVP

 

[straybullet]

Thank you both - going to experiment with both of those suggestions this weekend. My apologies for the layout of the table in my original post, it looked just right in the preview.

Let them hate - so long as they fear... Lucius Accius