multiple crosstabs in one query

[Guest]

Hi,

Does someone know how to do this (I use MSAccess 2000)? I am running a
crosstab query and want multiple columns to show up in the resulting query.

Original table:
KES yearcode WF01 WF04
01 1 22 33
01 2 19 30
02 1 20 32
02 2 21 29


I know how to do a crosstab for one column (field WF01):

TRANSFORM Avg([qry analyze].WF01) AS AvgOfWF01
SELECT [qry analyze].KES
FROM [qry analyze]
GROUP BY [qry analyze].KES
PIVOT [qry analyze].yearcode;

Results in:
KES 1 2
01 22 19
02 20 21

I would like to continue this table with the remaining column headings (I'll
rename the years 1 and 2 to reflect the variable, e.g. yr1WF01, yr2WF01,
yr1WF04, yr2WF04):

KES 1 2 1 2
01 22 19 33 30
02 20 21 32 29

 

[Duane Hookom]

Check this link for a possible solution
http://www.pcreview.co.uk/forums/thread-1182998.php