B
bbishop222
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");
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");