Switching row and colum - Is it possible?

[seaport]

I am using Access 2000. Is there any SQL statements or Access built-in functions that can switch the rows and columns of a table?<br><br>I have a table of expense data. The fields are &quot;Travel&quot;, &quot;Supplies&quot;, &quot;Equipments&quot; and etc. I input data quarterly. Therefore the primary key of this table is QuarterID, such as 2000/1, 2000/2, etc.<br>Now My boss needs an annually financial report. The format is that: 4 columns(each column for one quarter of the year), a dozon rows(each row for one expense).<br><br>All I can do now is to create a new table with fields expenseid, quarter1, quarter2, quarter3, quarter4. Then I write VB code to convert the data from my expense table to this new table.<br><br>Does someone has better idea?<br><br>Thanks in advance.<br><br>Seaport

 

[nightrider]

make a crosstab query

 

[seaport]

I am afraid that the crosstab query does not work for this problem. The crosstab query can convert the first column data of the original table into the column heading of the destination query. But it can not convert all column headings(field names) of the original table into the row heading (the first column data) of the destination query.<br><br>Seaport

 

[MichaelRed]

Yes, but a series of crosstabs CAN do it.&nbsp;&nbsp;It is not difficult unless you have a LARGE number of fields in the table.&nbsp;&nbsp;Just need to be careful about having a key to re-join the crosstab queries properly.&nbsp;&nbsp;For all but the trivial case, you probably want to do a make table of the joined crosstabs, as it can take Ms Access a while to regenerate the crosstab results.<br>