A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SHOWING MORE THAN ONE VALUE FIELD IN CROSSTAB QUERY



 
 
Thread Tools Display Modes
  #1  
Old December 2nd, 2009, 05:59 PM posted to microsoft.public.access.queries
Kingsoft
external usenet poster
 
Posts: 8
Default 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  
Old December 2nd, 2009, 06:23 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old December 2nd, 2009, 06:23 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old December 2nd, 2009, 06:23 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old December 2nd, 2009, 07:07 PM posted to microsoft.public.access.queries
Kingsoft
external usenet poster
 
Posts: 8
Default 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  
Old December 2nd, 2009, 07:07 PM posted to microsoft.public.access.queries
Kingsoft
external usenet poster
 
Posts: 8
Default 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  
Old December 2nd, 2009, 07:07 PM posted to microsoft.public.access.queries
Kingsoft
external usenet poster
 
Posts: 8
Default 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  
Old December 2nd, 2009, 07:53 PM posted to microsoft.public.access.queries
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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  
Old December 2nd, 2009, 07:53 PM posted to microsoft.public.access.queries
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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  
Old December 2nd, 2009, 07:53 PM posted to microsoft.public.access.queries
Duane Hookom
external usenet poster
 
Posts: 7,177
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:37 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.