Total in a Crosstab Query

[Guest]

I created a Crosstab Query that sums amounts by year for a given number of
years. I want to sum the years for each action that I have listed. I've
been playing around with it, and can't figure out how to do it because my
year is not an independent field. Any help would be appreciated. Here is
the query:

TRANSFORM Sum([BPItI 400 Report].N_MATL) AS SumOfN_MATL
SELECT [VL Code to Cluster Relationship].Cluster, [BPItI 400 Report].VLDESC,
[BPItI 400 Report].CLASS, [BPItI 400 Report].ACTION_DESC
FROM [BPItI 400 Report] INNER JOIN [VL Code to Cluster Relationship] ON
[BPItI 400 Report].VLCDE = [VL Code to Cluster Relationship].[VL Code]
WHERE ((([BPItI 400 Report].CLASS)="C" Or ([BPItI 400 Report].CLASS)="D" Or
([BPItI 400 Report].CLASS)="P" Or ([BPItI 400 Report].CLASS)="R" Or ([BPItI
400 Report].CLASS)="B" Or ([BPItI 400 Report].CLASS)="O") AND ((Year([BPItI
400 Report]![N_EFF_DTE]))="2010" Or (Year([BPItI 400
Report]![N_EFF_DTE]))="2006" Or (Year([BPItI 400 Report]![N_EFF_DTE]))="2007"
Or (Year([BPItI 400 Report]![N_EFF_DTE]))="2008" Or (Year([BPItI 400
Report]![N_EFF_DTE]))="2009"))
GROUP BY [VL Code to Cluster Relationship].Cluster, [BPItI 400
Report].VLDESC, [BPItI 400 Report].CLASS, [BPItI 400 Report].ACTION_DESC
ORDER BY [VL Code to Cluster Relationship].Cluster, [BPItI 400
Report].VLDESC, [BPItI 400 Report].CLASS, [BPItI 400 Report].ACTION_DESC
PIVOT Year([BPItI 400 Report]![N_EFF_DTE]) In
("2006","2007","2008","2009","2010");

 

[Duane Hookom]

I would get rid of all the " Or's " in your SQL. Also, the Year() function
returns a numeric so don't compare it to a string value.


TRANSFORM Sum([BPItI 400 Report].N_MATL) AS SumOfN_MATL

SELECT [VL Code to Cluster Relationship].Cluster, [BPItI 400 Report].VLDESC,
[BPItI 400 Report].CLASS, [BPItI 400 Report].ACTION_DESC,
Sum([BPItI 400 Report].N_MATL) As FiveYearTotal

FROM [BPItI 400 Report] INNER JOIN [VL Code to Cluster Relationship] ON
[BPItI 400 Report].VLCDE = [VL Code to Cluster Relationship].[VL Code]

WHERE [BPItI 400 Report].CLASS IN ("C","D","P","R","B","O")
AND Year([BPItI 400 Report]![N_EFF_DTE]) Between 2006 AND 2010

GROUP BY [VL Code to Cluster Relationship].Cluster,
[BPItI 400 Report].VLDESC, [BPItI 400 Report].CLASS,
[BPItI 400 Report].ACTION_DESC

ORDER BY [VL Code to Cluster Relationship].Cluster,
[BPItI 400 Report].VLDESC, [BPItI 400 Report].CLASS,
[BPItI 400 Report].ACTION_DESC

PIVOT Year([BPItI 400 Report]![N_EFF_DTE])
In (2006,2007,2008,2009,2010);