conversion of columns in row in MS Access

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

conversion of columns in row in MS Access

Author

Topic

manojbiswas
Starting Member

4 Posts

Posted - 2008-08-22 : 02:24:21

I have a query whcih give results like as below,

SumOfMONTH1 SumOfMONTH2 SumOfMONTH3 SumOfMONTH4 SumOfMONTH5 .....
1.2 2.4 3.4 5 6.5

And the MONTH1, MONTH2, MONTH3... are DEC'07, JAN'08, FEB'08 ... respectively.

I want to convert the columns of the above query in rows as below,

MONTHS VALUES
DEC'07 1.2
JAN'08 2.4
FEB'08 3.4
MAR'08 5
APR'08 6.5

I need to do this for creating month versus value chart using chart wizerd report.

I hope I could tell my problem clearly. Please help me out.

I could I solve the proble.

Regards,

Manoj Biswas

Sequin
Starting Member

25 Posts

Posted - 2008-08-22 : 04:48:36

I would look to the source of the data for the original query - from the looks of it, it is a crosstab (aka Pivot) query. Rather than dealing with the results of this query it would be much easier to go back to the data from which this query derives its data.

If you cannot do this, you could use a query like this (using your own query name instead of QRYCrossTab):

Select "DEC'07" as Month, SumOfMonth1 as "Value"
From QRYCrossTab
UNION
Select "JAN'08" as Month, SumOfMonth2 as "Value"
From QRYCrossTab
UNION
Select "FEB'08" as Month, SumOfMonth3 as "Value"
From QRYCrossTab
.................Other months tagged on the end in the same format

manojbiswas
Starting Member

4 Posts

Posted - 2008-08-22 : 06:54:06

Thank you very much for your reply. your suggestion worked till I do it for 10th month. After 10th month it is showing a message "Query is too complex". I need to do it for 36 months.

please suggest.

Regards,

Manoj Biswas

Sequin
Starting Member

25 Posts

Posted - 2008-08-22 : 09:02:55

You need to look to your original data on which the first query is based, not the result of the query. If the table is structured in this way it is bad design

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources