SHOWING MORE THAN ONE VALUE FIELD IN CROSSTAB QUERY

[Kingsoft]

I am atempting to use the crosstab query to display a series of data across
various dates, but can only get one value field of data to work. Is there a
way to display more than one value field?

 

[KARL DEWEY]

There are several ways but it usually needs a temp table. Post your crosstab
SQL and sample data.

 

[Kingsoft]

TRANSFORM [SumOfINDV HRS ERN]/[SumOfHRS WRK]*100 AS [INDEX]
SELECT [ASSOC DETAIL].WE, [ASSOC DETAIL].TEAM, [ASSOC DETAIL].SHIFT, [ASSOC
DETAIL].NAME1, Sum([ASSOC DETAIL].[HRS WRK]) AS [SumOfHRS WRK], Sum([ASSOC
DETAIL].[INDV HRS ERN]) AS [SumOfINDV HRS ERN], Sum([ASSOC DETAIL].UNITS) AS
SumOfUNITS
FROM [ASSOC DETAIL]
WHERE ((([ASSOC DETAIL].WE)=#11/28/2009#))
GROUP BY [ASSOC DETAIL].WE, [ASSOC DETAIL].TEAM, [ASSOC DETAIL].SHIFT,
[ASSOC DETAIL].NAME1
PIVOT [ASSOC DETAIL].DATE;

sample data (Note: I would like to show all of the fields that are being
summed, as crosstab (value) fields.
WE TEAM SHIFT NAME1 SumOfHRS WRK SumOfINDV HRS
ERN SumOfUNITS 11/23/2009 11/24/2009 11/25/2009
11/28/2009 A 1 Anthony
Crawford 13 14.3067607473547 8764 110.052005748882 110.052005748882
11/28/2009 A 1 Bernaldina Garcia
Ortiz 16 17.9311272338005 11594 112.069545211253 112.069545211253
11/28/2009 A 1 Carolyn
Hughes 16 17.9311272338005 11594 112.069545211253 112.069545211253
11/28/2009 A 1 Daysi
Caballero 16 17.9311272338005 11594 112.069545211253 112.069545211253
11/28/2009 A 1 Jose
Burgos 5.33333333333333 5.97704241126683 11594 112.069545211253 112.069545211253
11/28/2009 A 1 Karen
Watkins 16 17.9311272338005 11594 112.069545211253 112.069545211253
11/28/2009 A 1 Kookie
Turner 16 17.9311272338005 11594 112.069545211253 112.069545211253
11/28/2009 A 1 Louisa
Lopez-Lopez 16 17.9311272338005 11594 112.069545211253 112.069545211253
11/28/2009 A 1 Noberto
Cruz 16 17.9311272338005 11594 112.069545211253 112.069545211253

 

[Duane Hookom]

There is a solution at http://www.tek-tips.com/faqs.cfm?fid=4524.

--
Duane Hookom
Microsoft Access MVP

TRANSFORM [SumOfINDV HRS ERN]/[SumOfHRS WRK]*100 AS [INDEX]
SELECT [ASSOC DETAIL].WE, [ASSOC DETAIL].TEAM, [ASSOC DETAIL].SHIFT, [ASSOC
DETAIL].NAME1, Sum([ASSOC DETAIL].[HRS WRK]) AS [SumOfHRS WRK], Sum([ASSOC
DETAIL].[INDV HRS ERN]) AS [SumOfINDV HRS ERN], Sum([ASSOC DETAIL].UNITS) AS
SumOfUNITS
FROM [ASSOC DETAIL]
WHERE ((([ASSOC DETAIL].WE)=#11/28/2009#))
GROUP BY [ASSOC DETAIL].WE, [ASSOC DETAIL].TEAM, [ASSOC DETAIL].SHIFT,
[ASSOC DETAIL].NAME1
PIVOT [ASSOC DETAIL].DATE;

sample data (Note: I would like to show all of the fields that are being
summed, as crosstab (value) fields.
WE TEAM SHIFT NAME1 SumOfHRS WRK SumOfINDV HRS
ERN SumOfUNITS 11/23/2009 11/24/2009 11/25/2009
11/28/2009 A 1 Anthony
Crawford 13 14.3067607473547 8764 110.052005748882 110.052005748882
11/28/2009 A 1 Bernaldina Garcia
Ortiz 16 17.9311272338005 11594 112.069545211253 112.069545211253
11/28/2009 A 1 Carolyn
Hughes 16 17.9311272338005 11594 112.069545211253 112.069545211253
11/28/2009 A 1 Daysi
Caballero 16 17.9311272338005 11594 112.069545211253 112.069545211253
11/28/2009 A 1 Jose
Burgos 5.33333333333333 5.97704241126683 11594 112.069545211253 112.069545211253
11/28/2009 A 1 Karen
Watkins 16 17.9311272338005 11594 112.069545211253 112.069545211253
11/28/2009 A 1 Kookie
Turner 16 17.9311272338005 11594 112.069545211253 112.069545211253
11/28/2009 A 1 Louisa
Lopez-Lopez 16 17.9311272338005 11594 112.069545211253 112.069545211253
11/28/2009 A 1 Noberto
Cruz 16 17.9311272338005 11594 112.069545211253 112.069545211253
--
kingsoft

There are several ways but it usually needs a temp table. Post your crosstab
SQL and sample data.