Rearrange Row data to Column data for pivot charts

[b_bill]

I am having trouble arranging my data from a record row into a query that will give me columns for the pivot charts that I need. My guess is that I need some sort of a complicated SQL statement, and I know almost nothing about SQL. I have expressed the problem better, and with graphics, here: http://www.olypen.com/bhardin/RGB/RGB.htm. I would love to hear the solution to this aggravating problem.

 

[gromit]

hi Bill - Welcome!

Yes, you can write a query to generate the transpose (switch row to column).

It will look something like this...

SELECT Player1, 'Red' As Symbol, 'Yes' as Value1, YesRed AS Count
FROM tbllWl_Games
UNION 
SELECT Player1, 'Red' As Symbol, 'No' as Value1, NoRed AS Count
FROM tbllWl_Games
UNION 
SELECT Player1, 'Red' As Symbol, 'Mbe' as Value1, MbeRed AS Count
FROM tbllWl_Games
.
.
.
(Keep going for other colors)

Note: I used "Value1" rather than Value, since it is a reserved word and Access complained about it.

And no, I am not smart enough to come up with this on my own. I copied almost directly from http://p2p.wrox.com/topic.asp?TOPIC_ID=27962

Credit where credit is due!

Post back if you are still stuck.

-gromit

 

[b_bill]

Gromit,

Thanks for the help. After a bit of screen print work today, I will start following your directions, and thanks for the offer to post again if still stuck. I will look at the WROX example as well.

I'm surprised that in all my reading and Access study I have never come across a problem like this.

Cheers,
Bill

 

[gromit]

Glad to help. Probably one of the reasons that it is not more common is that good table design (normalized) generally points to one or the other arrangements.

- g

 

[b_bill]

Magnificent Mentoring!

Hi Gromit,

Thanks so much for your help! Your simple, straight forward example ended hours and hours of 'Access Agony'. It worked flawlessly! I also looked at the WROX forum where you said 'credit was due', but that was a case of transposing columns to columns, not rows to columns; it wasn't straight forward enough for me to gleen anything from. You slight yourself.

Thanks again ,
Bill