Trouble with PIVOT stmt in a XTab query 1

[VickyC]

Greetings. I am having trouble with the PIVOT statement in a cross-tab query.

Given table tblX__NORM below, I want to produce the query output below that.

tblX__NORM

ASch	ARow	X
1	1	1
1	2	1
1	3	1
1	4	0
1	5	1
1	6	1
1	7	1
1	8	0
1	9	0
1	10	0
1	11	1
1	12	0
1	13	0
1	14	0
2	1	1
2	2	1
2	3	1
2	4	0
etc...

desired query output

ASch	R01	R02	R03	R04	R05	R06	R07	R08	R09	R10	R11	R12	R13	R14
1	1	1	1	0	1	1	1	0	0	0	1	0	0	0
2	1	1	1	0	1	1	0	1	0	0	1	0	0	0
etc...

Here's my code...

TRANSFORM 
	Sum(t.X) AS SumOfX
SELECT 
	t.ASch
FROM 
	tblX__NORM AS t
GROUP BY 
	t.ASch
PIVOT 
	'R' & RIGHT('0' & t.ARow, 2) IN (R01,R02,R03,R04,R05,R06,R07,R08,R09,R10,R11,R12,R13,R14);

BUT, I can't write the Pivot Statement correctly. The code above gives the ERROR MESSAGE ...

Compile error. in query expression ''R' & RIGHT('0' & t.ARow, 2)'.


Any help appreciated.
Vicky

 

[SkipVought]

HI,

How about...

PIVOT 'R' & Format(a.tRow, "00")

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[VickyC]

hi Skip - you're format suggestion worked.

What puzzled me was that my code worked in the past. I moved all tables, queries... into a fresh mdb file, and now my original code works once again.

Thanks for taking the time
Vicky

 

[SkipVought]

And this should work too..

	'R' & RIGHT('0' & t.ARow, 2)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]