Having trouble getting a DSum calculated field to produce a running total for grouped data. The table below is an excel spreadsheet that shows the result of my access query. The RT column calculates the total of "Sum of Totbush" for the year, but does not provide a running total by the fiscal month. The furthest column to the right is an excel formula that I placed in the spreadsheet just to show what I would like the "RT" column to display. The query correctly resets at the start of each CropYr.
The calculated field I use for the RT field is:
RT: Format(DSum("[Sum of Totbush]","[TransmasTotalsCornQ1]","CropYr=" & [CropYr] & " And No<=" & [No]),0)
The SQL view of the entire query is:
SELECT TransmasTotalsCornQ1.CropYr, TransmasTotalsCornQ1.[Sum Of Totbush], TransmasTotalsCornQ1.Month, TransmasTotalsCornQ1.[No], DSum("[Sum of Totbush]","[TransmasTotalsCornQ1]","CropYr=" & [CropYr] & " And No<=" & [No]) AS RT, IIf([Month]<=5,[Month]+7,[Month]-5) AS FiscalMonth, MonthName([Month],True) AS MName
FROM TransmasTotalsCornQ1;
CropYr Sum Of Totbush Month No RT FiscalMonth MName RUNTot 2013 3245.7156 6 24161 445575.5326 1 Jun 3246 2013 16031.4334 8 24163 445575.5326 3 Aug 19277 2013 13978.5684 9 24164 445575.5326 4 Sep 33256 2013 113167.1395 10 24165 445575.5326 5 Oct 146423 2013 65176.4295 11 24166 445575.5326 6 Nov 211599 2013 87319.9955 12 24167 445575.5326 7 Dec 298919 2013 76078.0487 1 24168 445575.5326 8 Jan 374997 2013 37508.5726 2 24169 445575.5326 9 Feb 412506 2013 20373.5655 3 24170 445575.5326 10 Mar 432879 2013 11114.2783 4 24171 445575.5326 11 Apr 443994 2013 1581.7856 5 24172 445575.5326 12 May 445576 2014 5460.3487 6 24173 432500.0467 1 Jun 5460 2014 6218.5801 7 24174 432500.0467 2 Jul 11679 2014 13717.8557 8 24175 432500.0467 3 Aug 25397
Thanks in advance for your assistance. :banghead:
The calculated field I use for the RT field is:
RT: Format(DSum("[Sum of Totbush]","[TransmasTotalsCornQ1]","CropYr=" & [CropYr] & " And No<=" & [No]),0)
The SQL view of the entire query is:
SELECT TransmasTotalsCornQ1.CropYr, TransmasTotalsCornQ1.[Sum Of Totbush], TransmasTotalsCornQ1.Month, TransmasTotalsCornQ1.[No], DSum("[Sum of Totbush]","[TransmasTotalsCornQ1]","CropYr=" & [CropYr] & " And No<=" & [No]) AS RT, IIf([Month]<=5,[Month]+7,[Month]-5) AS FiscalMonth, MonthName([Month],True) AS MName
FROM TransmasTotalsCornQ1;
CropYr Sum Of Totbush Month No RT FiscalMonth MName RUNTot 2013 3245.7156 6 24161 445575.5326 1 Jun 3246 2013 16031.4334 8 24163 445575.5326 3 Aug 19277 2013 13978.5684 9 24164 445575.5326 4 Sep 33256 2013 113167.1395 10 24165 445575.5326 5 Oct 146423 2013 65176.4295 11 24166 445575.5326 6 Nov 211599 2013 87319.9955 12 24167 445575.5326 7 Dec 298919 2013 76078.0487 1 24168 445575.5326 8 Jan 374997 2013 37508.5726 2 24169 445575.5326 9 Feb 412506 2013 20373.5655 3 24170 445575.5326 10 Mar 432879 2013 11114.2783 4 24171 445575.5326 11 Apr 443994 2013 1581.7856 5 24172 445575.5326 12 May 445576 2014 5460.3487 6 24173 432500.0467 1 Jun 5460 2014 6218.5801 7 24174 432500.0467 2 Jul 11679 2014 13717.8557 8 24175 432500.0467 3 Aug 25397
Thanks in advance for your assistance. :banghead: