If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SHOWING MORE THAN ONE VALUE FIELD IN CROSSTAB QUERY
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? -- kingsoft |
#2
|
|||
|
|||
SHOWING MORE THAN ONE VALUE FIELD IN CROSSTAB QUERY
There are several ways but it usually needs a temp table. Post your crosstab
SQL and sample data. -- Build a little, test a little. "Kingsoft" wrote: 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? -- kingsoft |
#3
|
|||
|
|||
SHOWING MORE THAN ONE VALUE FIELD IN CROSSTAB QUERY
There are several ways but it usually needs a temp table. Post your crosstab
SQL and sample data. -- Build a little, test a little. "Kingsoft" wrote: 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? -- kingsoft |
#4
|
|||
|
|||
SHOWING MORE THAN ONE VALUE FIELD IN CROSSTAB QUERY
There are several ways but it usually needs a temp table. Post your crosstab
SQL and sample data. -- Build a little, test a little. "Kingsoft" wrote: 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? -- kingsoft |
#5
|
|||
|
|||
SHOWING MORE THAN ONE VALUE FIELD IN CROSSTAB QUERY
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 "KARL DEWEY" wrote: There are several ways but it usually needs a temp table. Post your crosstab SQL and sample data. -- Build a little, test a little. "Kingsoft" wrote: 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? -- kingsoft |
#6
|
|||
|
|||
SHOWING MORE THAN ONE VALUE FIELD IN CROSSTAB QUERY
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 "KARL DEWEY" wrote: There are several ways but it usually needs a temp table. Post your crosstab SQL and sample data. -- Build a little, test a little. "Kingsoft" wrote: 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? -- kingsoft |
#7
|
|||
|
|||
SHOWING MORE THAN ONE VALUE FIELD IN CROSSTAB QUERY
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 "KARL DEWEY" wrote: There are several ways but it usually needs a temp table. Post your crosstab SQL and sample data. -- Build a little, test a little. "Kingsoft" wrote: 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? -- kingsoft |
#8
|
|||
|
|||
SHOWING MORE THAN ONE VALUE FIELD IN CROSSTAB QUERY
There is a solution at http://www.tek-tips.com/faqs.cfm?fid=4524.
-- Duane Hookom Microsoft Access MVP "Kingsoft" wrote: 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 "KARL DEWEY" wrote: There are several ways but it usually needs a temp table. Post your crosstab SQL and sample data. -- Build a little, test a little. "Kingsoft" wrote: 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? -- kingsoft |
#9
|
|||
|
|||
SHOWING MORE THAN ONE VALUE FIELD IN CROSSTAB QUERY
There is a solution at http://www.tek-tips.com/faqs.cfm?fid=4524.
-- Duane Hookom Microsoft Access MVP "Kingsoft" wrote: 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 "KARL DEWEY" wrote: There are several ways but it usually needs a temp table. Post your crosstab SQL and sample data. -- Build a little, test a little. "Kingsoft" wrote: 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? -- kingsoft |
#10
|
|||
|
|||
SHOWING MORE THAN ONE VALUE FIELD IN CROSSTAB QUERY
There is a solution at http://www.tek-tips.com/faqs.cfm?fid=4524.
-- Duane Hookom Microsoft Access MVP "Kingsoft" wrote: 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 "KARL DEWEY" wrote: There are several ways but it usually needs a temp table. Post your crosstab SQL and sample data. -- Build a little, test a little. "Kingsoft" wrote: 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? -- kingsoft |
Thread Tools | |
Display Modes | |
|
|