DSum for running totals of groups. (1 Viewer)

[wrlgrain]

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:

 

[June7]

Instead of query and DSum, consider a report using its Sorting & Grouping features with aggregate calcs. Textbox in Report has RunningSum property.

 

[plog]

Can you post the data in TransmasTotalsCornQ1 that the spreadsheet data is based on?

 

[wrlgrain]

An Excel export of the query TransmasTotalsCornQ1 is attached.

 

[arnelgp]

you can use subquery:

...(select sum([sum of toothbrush]) As Expr1 from TransmasTotalsCornQ1 as T1 where (T1.[CropYr] & Format(T1.[FiscalMonth],"00") <= (TransmasTotalsCornQ1. [CropYr] & Format(TransmasTotalsCornQ1.[FiscalMonth],"00")) As RunTot From TransmasTotalsCornQ1 Order By [CropYr], [FiscalMonth];

 

[plog]

As often with data, your assumptions are incorrect. Looking at the DSUM criteria:

"CropYr=" & [CropYr] & " And No<=" & [No]

So the running sum is comprised of records that have the same CropYr and who's No value is less than or equal to the current record. Look at your No data and you will see that's not true:

April 2013 - 24171
May 2013 - 24172
June 2013 - 24161

June 2013 will be part of Mays running total because its June's No value is less than May's No value. You need to replace the No comparison in the DSum with a valid way to order your data--most likely with an actual date field or a numeric month field.

However, you might want to check your CropYr to make sure you are calculating it correctly. No, might be the right field to use but you could be calculating CropYr incorrectly.

 

[wrlgrain]

Thanks for your comments.



Our fiscal year (ie CropYr) runs from June of one year to May of the following year. I am trying to get a running total beginning in June and ending in May. For this example, the calendar dates for June to December would be in 2013, while the calendar dates from January through May would be in 2014. The calculated No field was a way to sort the data in the correct ascending order from June to May. In your reply, you show that June 2013 has a lower No than does April 2013. This is correct since June is the first month of our fiscal year. The number 24171 refers to calendar month/year of April 2014 which is after calendar month/year June 2013. It's confusing, but remember the year shown is not the calendar year, but the crop year.



The DSum formula used was to compare the calculated No to the previous record's No and and as long as the CropYr was the same as the previous record and the No was equal or less than the previous number, a running total of the the Sum of Totbush would be generated for that line.


Hope this explanation helps.

 

[plog]

The DSUM looks correct then. So what's the issue then? What results is your running sum producing?

 

[wrlgrain]

See attached excel spreadsheet. Columns A - G represent the results of the access query. Column H was added to the spreadsheet to show what column E, "RT", should show.


In the spreadsheet the "RT" value for No 24161 is 445575 which is the grand running total for entire crop year of 2013 from June - May. The value that should be shown is 3246. The next record which has the No 24163 shows the RT value again of 445575. It should show a value of 19277 which would be the running total of the first two records for No 24161 and 24163. When you get to No 24173, you have a new crop year so the running total should reset since this is the first fiscal month of a new crop year and therefore should be 5460.

 

[arnelgp]

see module1 for the function i used in the query.
see query, query1 in design view how i called the function.

 

[plog]

Are you sure you are using the No criteria in the DSUM? The result you are producing makes it seem like the below is your DSum:

DSum("[Sum of Totbush]","[TransmasTotalsCornQ1]","CropYr=" & [CropYr]

Can you post a copy of your database if that is not the case?

 

[wrlgrain]

Thank you so much. It looks like your query will do what I am looking for. I will give it a try.

 

[wrlgrain]

The DSum formula that was being used was the following. The post from plog appears to be a workable solution. I will give it a try.



RT: Format(DSum("[Sum of Totbush]","[TransmasTotalsCornQ1]","CropYr=" & [CropYr] & " And No<=" & [No]),0)

 

[wrlgrain]

I meant to say the post from arnelgp. Thanks to everyone for their assistance.

 

[wrlgrain]

In your Zip file, I only find query1, the table and the module. Was there also supposed to one called query? Will the module work if TransmasTotalsCornQ2 is a query rather than a table?

 

[arnelgp]

It eill work on your query. Just replace the table name in the function with your query name.

 

[wrlgrain]

When I added the module to my database and changed the TransmasTotalsCornQ2 to reflect your solution, I get a compile error. See attached. Any thoughts

 

[arnelgp]

on vba menu, Tools->Reference, check if there are any missing reference and try to resolve it.

 

[wrlgrain]

Was able to get it working by clicking on the following in the Tools, Reference section of module3:


Microsoft Office 16.0 Access database engine Object Library

Thanks again for your assistance
​

 

[wrlgrain]

In the module, you define a new function for runtotal using the following code:


With CurrentDb.OpenRecordset( _
"select [Sum Of Totbush] as expr1, [fiscalmonth] from [TransmasTotalsCornQ2] " & _
"where [cropyr] = " & CropYear & " order by [cropyr], [fiscalmonth];")


My question is that I need to also run the same or similar code for the commodities"Soybeans" and "Wheat". The data for these products reside in the query [TransmasTotalsBeansQ2] and [TransmasTotalsWheatQ2]. Should I create 3 different modules each one that has the correct query referenced or use something like a Select Case statement.


Not sure the best way or the correct syntax.



Thanks for your continued assistance.