Complex Crosstab Query? 2

[RonMcNatt]

This request seems simple, but its killing me. A normal query returs the information as listed below. I would like the result set to be formatted like the bottom example. If I pick a singe column (DelVol for example) I can use a crosstab query to show in the bottom format, but I can't find a way to list the other columns. Any help would be greatly appreciate.

DelVol DelStops PUVol PUStops WeekEnding
Store A 5 6 7 8 4/7/07
Store A 5 6 7 8 4/14/07
Store A 5 6 7 8 4/21/07


Store A
4/7/07 4/14/07 4/21/07 4/28/07
DelVol 5 5 5 5
DelStops 6 6 6 6
PUVol 7 7 7 7
PUStops 8 8 8 8

 

[PHV]

Have you tried to base your crosstab query on an union query ?
SELECT Store, 'DelVol' As Type, DelVol As Value, WeekEnding FROM yourTable
UNION ALL SELECT Store, 'DelStops', DelStops, WeekEnding FROM yourTable
UNION ALL SELECT Store, 'PUVol', PUVol, WeekEnding FROM yourTable
UNION ALL SELECT Store, 'PUStops', PUStops, WeekEnding FROM yourTable

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886