Transpose query, Rows to Columns 1

[GKIL67]

I have the table tblpatskips:

DrawNo	BALL	SKIPS
1	5	3
1	12	16
1	18	2
1	20	11
1	32	7
2	2	3
2	6	1
2	23	1
2	24	9
2	42	2

Where DrawNo is an ID counter, BALL values are 1-45 and
SKIPS can be any number. I need to transform it to:

DrawNo	BALL1	BALL2	BALL3	BALL4	BALL5	SKIPS1	SKIPS2	SKIPS3	SKIPS4	SKIPS5
1	5	12	18	20	32	3	16	2	11	7
2	2	6	23	24	42	3	1	1	9	2

There are always five(5) BALLS and five(5) SKIPS per
DrawNo and it is rather important to retain the specific
headings.

Could anyone provide some useful advice?
Many Thanks!

 

[PHV]

Create a query named, say, qrypatskipsRank:

SELECT A.DrawNo, A.BALL, A.SKIPS, Count(*) AS Rank
FROM tblpatskips AS A INNER JOIN tblpatskips AS B ON A.DrawNo=B.DrawNo AND A.BALL>=B.BALL
GROUP BY A.DrawNo, A.BALL, A.SKIPS

And now, your transpose query:

SELECT R1.DrawNo, R1.BALL AS Ball1, R2.BALL AS Ball2, R3.BALL AS Ball3, R4.BALL AS Ball4, R5.BALL AS Ball5
, R1.SKIPS AS Skips1, R2.SKIPS AS Skips2, R3.SKIPS AS Skips3, R4.SKIPS AS Skips4, R5.SKIPS AS Skips5
FROM (((qrypatskipsRank AS R1 
INNER JOIN qrypatskipsRank AS R2 ON R1.DrawNo = R2.DrawNo) 
INNER JOIN qrypatskipsRank AS R3 ON R2.DrawNo = R3.DrawNo) 
INNER JOIN qrypatskipsRank AS R4 ON R3.DrawNo = R4.DrawNo) 
INNER JOIN qrypatskipsRank AS R5 ON R4.DrawNo = R5.DrawNo
WHERE R1.Rank=1 AND R2.Rank=2 AND R3.Rank=3 AND R4.Rank=4 AND R5.Rank=5

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

 

[GKIL67]

It works, straight to the point!
Thank You so much for your help!