Transposing data in Access

[jnmtt]

I am currently designing a database to manage a collection of economic data. The data is arranged as panel data, meaning that the years (1991-2001) are a field by itself, while each data series (GDP, inflation, etc) are also fields. This is necessary to perform calculations on the data (debt/gdp as a %, etc.). However, I would like to display reports in a cross-section format:

1995 1996 1997 1998
GDP xxx xxx xxx xxx
Inflation xxx xxx xxx xxx

and so on. Given that the years are also a field, reports from queries I have generated look like:

GDP Inflation
1995 xxx xxx
1996 xxx xxx
1997 xxx xxx
1998 xxx xxx

Is it possible to transpose the data in the query to generate the desired format?

 

[petersdaniel]

You need to perform a cross-tab query in Access in order to pivot your information.

 

[jnmtt]

A cross tab query will only allow three row headings, is it possible to increase this to more?

 

[petersdaniel]

There are no limits on the nuumber of row headings that you can have. it is a limitation imposed by the Wizard. There is a limit on the number of column heading / values petersdaniel@hotmail.com
"If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut." --Albert Einstein