computed total column in crosstab query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have constructed a crosstab query that works right. Now, I want to add a
field that totals the values in the crosstab.

I added field total_kpi_ElapsedTime_so_to_bo_days with a formula exactly
like the one used to calculate the column values, but it apparently gives the
wrong result. What I want is just the sum of the values.

Can anyone assist in this matter?
Thanks in Advance.
Steve.

sql follows:
TRANSFORM Sum([kpi_elapsedtime_so_to_bo])/Count([kpi_elapsedtime_so_to_bo])
AS kpi_ElapsedTime_so_to_bo_days
SELECT qsKpiLeadTimes.Country, qsKpiLeadTimes.zr_Plnt,
Sum(Sum([kpi_elapsedtime_so_to_bo])/Count([kpi_elapsedtime_so_to_bo])) AS
total_kpi_ElapsedTime_so_to_bo_days
FROM qsKpiLeadTimes
WHERE (((qsKpiLeadTimes.bo_Docdate) Is Not Null))
GROUP BY qsKpiLeadTimes.Country, qsKpiLeadTimes.zr_Plnt
PIVOT qsKpiLeadTimes.con_ShippingType In ("Air, Hot","Air, Regular","Air,
Rig Down","Ocean","Mix");
 
Steve,

you are going to have to create another query to get the totals, grouped by
the same fields as are included in the RowHeading portion of your crosstab
query. Once you save that, you can join it to the crosstab on the
appropriate fields and create the final query.

HTH
Dale
 
Back
Top