Urgent Repost: Crosstab Calculation Anguish

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Hi:
I am using Access 2000.
In a nutshell, I need to take the results from my
crosstab, with custom column headings, and add some of the
results together for use in my report.My crosstab SQl is
as follows:

TRANSFORM Avg(qryDaysToApproval.Days) AS AvgOfDays
SELECT qryDaysToApproval.bytWorkArea, Avg
(qryDaysToApproval.Days) AS [Average Of Days]FROM
qryDaysToApproval
GROUP BY qryDaysToApproval.bytWorkAreaPIVOT
qryDaysToApproval.strType In
("DRS","EZE","GEO","LOC","MLL","MLP","MSC","MSL","PIL","PLA
","REC","ROE","SMC","SME","SML");

For example: I need to find the average of the values of
DRS and EZE and GEO from Work Area 1. The crosstab is
finding the correct individual values but I do not know
how to combine them together as needed.

Is the crosstab making the values text because the type
field is text? If so, how do I convert this and do I
convert it in a query or in the report?Thanks for your
help.
 
You are grouping by a Row Heading of bytWorkArea. However, you only want to
average the three columns for one Work Area. Is this correct? I think you
could average the columns by WorkArea using:
DEGAvg:Avg(IIf(strType = "DRS" or strType = "EZE" or strType="GEO",
[Days],Null))
Expression
Row Heading

If the returned values are strings then use:
TRANSFORM Val(Avg(qryDaysToApproval.Days)) AS AvgOfDays
 
Awesome, thanks Duane.

I'll give it a shot.
-----Original Message-----
You are grouping by a Row Heading of bytWorkArea. However, you only want to
average the three columns for one Work Area. Is this correct? I think you
could average the columns by WorkArea using:
DEGAvg:Avg(IIf(strType = "DRS" or strType = "EZE" or strType="GEO",
[Days],Null))
Expression
Row Heading

If the returned values are strings then use:
TRANSFORM Val(Avg(qryDaysToApproval.Days)) AS AvgOfDays
--
Duane Hookom
MS Access MVP


Hi:
I am using Access 2000.
In a nutshell, I need to take the results from my
crosstab, with custom column headings, and add some of the
results together for use in my report.My crosstab SQl is
as follows:

TRANSFORM Avg(qryDaysToApproval.Days) AS AvgOfDays
SELECT qryDaysToApproval.bytWorkArea, Avg
(qryDaysToApproval.Days) AS [Average Of Days]FROM
qryDaysToApproval
GROUP BY qryDaysToApproval.bytWorkAreaPIVOT
qryDaysToApproval.strType In
("DRS","EZE","GEO","LOC","MLL","MLP","MSC","MSL","PIL","PLA
","REC","ROE","SMC","SME","SML");

For example: I need to find the average of the values of
DRS and EZE and GEO from Work Area 1. The crosstab is
finding the correct individual values but I do not know
how to combine them together as needed.

Is the crosstab making the values text because the type
field is text? If so, how do I convert this and do I
convert it in a query or in the report?Thanks for your
help.
Click to expand...


.
Click to expand...
 
Back
Top