Crosstab multiple Columns

[Guest]

I have data in this form (very simplified)

Week Dept Projects Hours
1 2100 4 160
2 2100 5 180
1 2200 6 210
2 2200 7 200

What I want is to build a crosstab that produces these results:
Dept Wk1-Proj Wk2-Proj Wk1-Hrs Wk2-Hrs
2100 4 5 160 180
2200 6 7 210 200

Does anyone know how to accomplish this with a Crosstab Query? Could I use 2
Crosstabs and them join them?

Thanks
JohnV

 

[Duane Hookom]

Try search google groups with
multi-value crosstab group:*Access.Queries* author:duane author:hookom

 

[MGFoster]

I have data in this form (very simplified)

Week Dept Projects Hours
1 2100 4 160
2 2100 5 180
1 2200 6 210
2 2200 7 200

What I want is to build a crosstab that produces these results:
Dept Wk1-Proj Wk2-Proj Wk1-Hrs Wk2-Hrs
2100 4 5 160 180
2200 6 7 210 200

Does anyone know how to accomplish this with a Crosstab Query? Could I use 2
Crosstabs and them join them?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could do it this way:

TRANSFORM Sum(Hours) AS SumOfHours
SELECT Dept
FROM ProjectHours -- change to your table's name
GROUP BY Dept
PIVOT [Week] & "/" & [Project]

Which will give a result like this:

Dept 1/4 1/6 2/5 2/7
- ---- --- --- --- ---
2100 160 180
2200 210 200

The 1/4 means Week 1, Project 4.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQt1BRIechKqOuFEgEQJr6gCg9i64S/blcZQQ2L1LEliDHUE06hQAn3wV
Oa7d7r5bs7CuXRC5n3Z2M8KF
=1b3q
-----END PGP SIGNATURE-----