Tramsforming data from columns/rows to rows/columns 1

[egstatus]

Hi all, I have this table in MS ACCESS 2003, The table looks like this: [total of 11 rows and 38 columns. I will only show partial data for the sample given]

DIST AVG_SPENT MED_AGE 1ST_TIME WEEKLY MONTHLY ...
0 $10.25 25 12 10 25
1 $50.25 23 20 30 35
2 $52.25 30 22 45 80
.... [TOTAL OF 11 DISTRICTS/ROWS]

I WANT TO CHANGE THE TABLE SO IT LOOKS LIKE THIS

DIST 0 DIST 1 DIST 2 DIST 3 DIST 4
AVG_SPENT $10.25 $50.25 $52.25
MED_AGE 25 23 30
1ST_TIME 12 20 22
WEEKLY 10 30 45
MONTHLY 25 35 80

How do I acomplish this programmatically. Right now I am doing it manually and it takes alot of time. I have tryed an array but with no much luck.

Thanks in advance.

 

[Remou]

Have a look at Crosstab queries.

 

[egstatus]

I have tryed that too but the problem with crosstab querries is that it only lets you have a maximum of 3 rows [row headings per table, my table would end up with 38 row headings]unless I am doing it wrong.
That is why I am searching doing it programmatically.

Thanks

 

[Remou]

How about two queries? First:

SELECT "Avg_Spent" As RHead, Dist, Avg_Spent As Data From Dist
UNION SELECT "Med_Age" As RHead, Dist, Med_Age As Data From Dist
<...>
UNION SELECT "Col_38" As RHead, Dist, Col_38 As Data From Dist

Then:

TRANSFORM Sum(Q1.Data) AS SumOfData
SELECT Q1.RHead, Sum(Q1.Data) AS [Total Of Data]
FROM Q1
GROUP BY Q1.RHead
PIVOT Q1.Dist

 

[egstatus]

Thanks very much, that did it.

Ed