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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Total in a Crosstab Query
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"); |
#2
|
|||
|
|||
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); -- Duane Hookom MS Access MVP -- "bbishop222" wrote in message ... 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"); |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Grand Total Problem | PW11111 | Running & Setting Up Queries | 2 | March 11th, 2005 05:44 PM |
Nested in-line Query | laura | Running & Setting Up Queries | 0 | February 11th, 2005 12:17 AM |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
count number of records | Joe_Access | General Discussion | 1 | January 13th, 2005 06:27 PM |
incorrect sums in report using 2 tables | jkendrick75 | Setting Up & Running Reports | 22 | December 13th, 2004 02:19 PM |