Reversing a crosstab

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that is laid out in a crosstab format. Using a union query i
have been able to take the colums and join them but i can't seem to create
the field that in a crosstab query would be the column headings.

So how do I add a new field to the query, call it "Month" and populate the
records with "Oct" or whatever month I need. The code I have so far is below:

Select [Year],[PC],[LC],[DC],[GLAC],[Oct] AS Amount
From [FMBS Import]
UNION ALL Select [Year],[PC],[LC],[DC],[GLAC],[Nov]
From [FMBS Import];

The "Month" needs to follow "GLAC"

Thanks in advance,
Bryan
 
Access wasn't designed to perform this function. I typically do it with
VBA, a series of queries, and/or both.
 
I have a table that is laid out in a crosstab format. Using a union query i
have been able to take the colums and join them but i can't seem to create
the field that in a crosstab query would be the column headings.

So how do I add a new field to the query, call it "Month" and populate the
records with "Oct" or whatever month I need. The code I have so far is below:

Select [Year],[PC],[LC],[DC],[GLAC],[Oct] AS Amount
From [FMBS Import]
UNION ALL Select [Year],[PC],[LC],[DC],[GLAC],[Nov]
From [FMBS Import];

The "Month" needs to follow "GLAC"

Thanks in advance,
Bryan
Click to expand...

Just use a text literal:

Select [Year],[PC],[LC],[DC],[GLAC],"Oct"

"Amount" seems a strange fieldname for a text string representing a
month but that's your choice! Might you not instead want to use a
Date/Time field, including the year, so that October 2004 data doesn't
get mixed up with October 2005?

John W. Vinson[MVP]
 
Found the solution and would like to share it. I may not have posted my
original intent well enough so let me also try again.

I have a table data that looks like this:
Code Oct Nov Dec
XXX $Value $Value $Value
YYY $Value $Value $Value

What I need is to put this in a different format which is kind of like
reversing a crosstab to look like this:

Code Month Amount
xxx Oct $Value
xxx Nov $Value
xxx Dec $Value
yyy Oct $Value
yyy Nov $Value
yyy Dec $Value

The challange was that i needed to add a field called "Month" and name the
field with the dollar values "Amount". I used a union query as follows:

Select
Code: 
,"Oct" AS [Month],[Oct] AS [Amount]
From [FMBS Import]
UNION ALL
Select [Code],"Nov" AS [Month],[Nov] AS [Amount]
From [FMBS Import]
UNION ALL... through Sep.
 
Back
Top