high qty column table data rotation

[robbydogg]

Hello All,

I thought when i gave up on this it was just a Friday afternoon moment, but it seems to have lasted into today .

I have a table with 242 rows (relating to retailer numbers) and 64 columns (relating to retailer answers to the questions.

What i need is to be able to show the following (but using a query to select which retailers are shown):

Question Retailer 1 Retailer 2 Retailer 3 etc etc
Q1 121 221 223
Q2 11221 221 253
Q3 1211 21 423


can anyone help? i've tried using a cross tab query but no success as there is various values which occur in the table .

help would be greatly appreciated as i'm stumped.

 

[neileg]

You have an unnormalised structure. You should have one record for each retailer/question combination. Then you'd be able to run a cross tab.

 

[robbydogg]

Hi,
It appears my formatting was lost in the first post.
Basically i have the following :

Image1.jpg

http://img155.imageshack.us/my.php?image=image1kf6.jpg


Where 107 302 etc are the retailer numbers and RFS01 RFS02 etc are the question references.

If possible i need to run a query to select certain retailers (I can do that bit ok) then have it turned like so:

image2.jpg

http://img409.imageshack.us/my.php?image=image2kw4.jpg


but i just can;t seem to suss it .

 

[neileg]

Did you ignore my advice or just not understand???

 

[robbydogg]

hi, sorry - i didn't ignore the advice, i just thought i wasn;t clear of what data i was showing.
Does this mean i'm going to have to totally re-design the database input table?

 

[neileg]

That's the way I see it! You could simulate the proper design by creating 64 select queries and union them together.

I don't do questionnaire type apps and it's a common topic so it may be worth searching in these forums in case I'm way off on this. It has been known!