[AccessD] Crosstab question

Hi Oleg

> Hey guys, I am having trouble writing a crosstab query, what I have now, is

> FILTER  Mo1  Mo2  Mo3  Mo4-6  Mo7-12 Yr2
> aaa      1    2    2     22     11    2
> bbb      1    4    4     33     33    3
> ccc      1    1    1     11     11    1
> ddd      2    2    2      2      2    2   etc.

> I need it in this format --

> DATE aaa bbb ccc
> Mo1   1   1   1
> Mo2   2   4   1
> Mo3

> I am using crosstab querry, in row heading I put FILTER, what do I put in
> Column heading ?

You can use a union query to collect the values column by column:

SELECT
  "Mo1" AS Period, Filter,
  [Mo1] AS Amount
FROM
  tblTable
UNION SELECT
  "Mo2" AS Period, Filter,
  [Mo2] AS Amount
FROM
  tblTable
UNION SELECT
  "Mo3" AS Period, Filter,
  [Mo3] AS Amount
FROM
  tblTable
UNION SELECT
  "Mo4-6" AS Period, Filter,
  [Mo4-6] AS Amount
FROM
  tblTable
UNION SELECT
  "Mo7-12" AS Period, Filter,
  [Mo7-12] AS Amount
FROM
  tblTable
UNION SELECT
  "Yr2" AS Period, Filter,
  [Yr2] AS Amount
FROM
  tblTable;


Then feed this to a crosstab query:

TRANSFORM
  Sum(Amount)
SELECT
  Period
FROM
  qdyUnion
GROUP BY
  Period
PIVOT
  Filter;

Not a speed daemon but it works.

/gustav