How to transpose rows and columns in Access 2003?

Question

How do I transpose rows and columns in Access 2003? I have a multiple tables that I need to do this on.

(I've reworded my question because feedback tells me it was confusing how I originally stated it.)

Each table has 30 fields and 20 records.
Lets say my fields are Name, Weight, Zip Code, Quality4, Quality5, Quality6 through Quality30 which is favorite movie. Let's say the records each describe a person. The people are Alice, Betty, Chuck, Dave, Edward etc through Tommy..

I can easily make a report like this:

  >>Alice...120....35055---etc, etc, etc...Jaws  
  Betty....125....35212...etc, etc, etc...StarWars  
  etc  
  etc  
  etc  
  Tommy...200...35213...etc, etc, etc...Adaptation

But what I would like to do is transpose those rows and columns so my report displays like this

  >>Alice........Betty......etc,etc,etc...Tommy  
  120.........125........etc, etc, etc...200  
  35055.....35212....etc, etc, etc...35213  
  etc   
  etc  
  etc  
  Jaws...StarWars..etc,etc,etc...Adaptation

Thanks for any help.

Answer 1

Access actually has a transpose verb, crosstab. You'll find good stuff if you search for "crosstab", which is the more common database-y way to say it. Like this Knowledge Base article. The generic database answers usually involve transpose and summarize. If you need a non-summary crosstab, it can be done, but you'll likely need a custom function.

Answer 2

TRANSFORM SUM(Weight)
SELECT "Dummy Data"
FROM myTable 
GROUP BY "Dummy Data"
PIVOT [Name]

Does this help?

EDIT: This assumes that names won't repeat.
i.e. There will not be more than 1 record for a given Name.

Answer 3

Thanks to all for your help.
I believe I have discovered the answer, and I put the link to it here in case someone else needs it in the future: http://support.microsoft.com/kb/202176
(This works for Access 2003.)

Answer 4

Do it in Excel if you're more comfortable there

Export back to Access when you're done

Answer 5

i have same problem at end , i Export table to Excel And do the transpose , Impart again but i still believe it must be anther way without going out of access 2003