UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dsum Function, Access 2016    
 
   
adampoz
post Apr 3 2017, 12:43 PM
Post#1



Posts: 5
Joined: 9-February 09



Hi,

I want to create a running total by year and month of the accumulated cost and my Dsum does not provide back the expected results. Any suggestions of where I am going wrong?

Running: DSum("[Purchase]","[New_Qtr]","[Month Number]<=" & [Month Number] & "and [Year]<=" & [Year] & "")

table

Year Month Number cost
2013 3 1.75
2013 7 4864.9
2013 8 4947.4
2013 10 8067.87
2014 1 2018
2014 3 1961.28
2014 4 -2021.19
2014 5 -3773
2014 9 1527.09
2015 2 4.68
2015 6 -273.4
2016 1 1029.67
2016 5 3286
2016 6 5826.86
2016 7 2655.5

thanks
Go to the top of the page
 
doctor9
post Apr 3 2017, 12:53 PM
Post#2


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


adampoz,

The problem is that when you're evaluating the March 2014 record, you're only summing the records that are for January, February or March of 2013 and 2014, and you're skipping the later months of the previous years.

You should convert the year and month number to a date, and then use the date as the comparison.

Also, ideally you should not have a field named "Year" as that is a reserved word, and could also cause problems. Spaces in field names can also cause problems.

Running: Ccur(DSum("curCost","New_Qtr","DateSerial(intYear, intMonthNumber, 1)<=#" & DateSerial([intYear], [intMonthNumber], 1) & "#"))

Hope this helps,

Dennis
Go to the top of the page
 
adampoz
post Apr 3 2017, 05:49 PM
Post#3



Posts: 5
Joined: 9-February 09



Hi Dennis

thanks for the information, I have changed the query and the field names given the issues that you stated.

The results I get back are a sum of each year on each month line rather than a running total, is there anyway that I can get this fixed?

thanks

Adam
Go to the top of the page
 
RJD
post Apr 3 2017, 08:33 PM
Post#4


UtterAccess VIP
Posts: 8,954
Joined: 25-October 10
From: Gulf South USA


Hi Adam: Pardon me for jumping in (I don't see Dennis in the house right now), but if you want the running sum in the query, Dennis' DSum seems to do that without a problem. I created a demo with the records you posted, then a query with Dennis' DSum. It creates the running sum.

This seems, however, to be the hard way to do this. You could put the records in a report and use the report's built-in Running Sum property on the cost field in another control, and get the same result. This approach is also included in the attached demo.

Take a look at the demo and see if this is what you tried to do with your db, or if we need more guidance on what you are trying to achieve.

HTH
Joe
Attached File(s)
Attached File  DSumFunction.zip ( 21.45K )Number of downloads: 6
 
Go to the top of the page
 
doctor9
post Apr 4 2017, 08:04 AM
Post#5


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


Adam,

That is odd - you'll need to make sure that the query is sorted by year, then month since that's how the DSum is set up.

Otherwise, RJD brings up a good point - depending on the purpose of this running sum, you may not need to do it within the query at all.

Hope this helps,

Dennis
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2018 - 08:57 PM