Query Help 1

[dj982020]

I have a table that looks like the following:

Ans Q1 Q2 Q3 Q4
a
1
2
3
4
b 1
2
3
4

But I want it to look like this:

Ans Q1 Q2 Q3 Q4
a 1 2 3 4
b 1 2 3 4

Is there anyway to do this?? Please Help!!

 

[PHV]

How are the Qi fields related to the right Ans ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dj982020]

I guess that's where I'm struggling. The only way you know is when a new value in Ans field. You know that all of the Qi values (up until the next Ans) belong to the above Ans.

 

[PHV]

How can you guarantee the row order ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dj982020]

It was given to me in this format with a sequential unique ID.

 

[PHV]

Something like this ?
SELECT A.Ans
, A.Q1 & B.Q1 & C.Q1 & D.Q1 & E.Q1 As Q1
, A.Q2 & B.Q2 & C.Q2 & D.Q2 & E.Q2 As Q2
, A.Q3 & B.Q3 & C.Q3 & D.Q3 & E.Q3 As Q3
, A.Q4 & B.Q4 & C.Q4 & D.Q4 & E.Q4 As Q4
FROM yourTable AS A
LEFT JOIN (yourTable AS B
LEFT JOIN (yourTable AS C
LEFT JOIN (yourTable AS D
LEFT JOIN yourTable AS E
ON D.ID=E.ID-1) ON C.ID=D.ID-1)
ON B.ID=C.ID-1) ON A.ID=B.ID-1
WHERE A.Ans>""
AND Trim(B.Ans & "")="" AND Trim(C.Ans & "")=""
AND Trim(D.Ans & "")="" AND Trim(E.Ans & "")=""
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244