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


UtterAccess Editor
Posts: 17,924
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

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
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: 7,833
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: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
doctor9
post Apr 4 2017, 08:04 AM
Post#5


UtterAccess Editor
Posts: 17,924
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

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 02:18 AM