INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Trouble with PIVOT stmt in a XTab query

Trouble with PIVOT stmt in a XTab query

(OP)
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.

CODE

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

CODE

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...

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

RE: Trouble with PIVOT stmt in a XTab query

HI,

How about...

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Trouble with PIVOT stmt in a XTab query

(OP)
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

RE: Trouble with PIVOT stmt in a XTab query

And this should work too..

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close