Question Re: Monthly Running Sum query

nebula13

New Member
Joined
Nov 20, 2009
Messages
19
Hello everything,
This seems like a great forum and its my first time posting so Ill do my best to be as clear as possible...
I have a table, lets call it "MyTable", it contains 3 fields:
ID (Autonumber), LogDate (Date), Volume (currency)

I need to create a query based on this table with two running volume totals, Month to Date ("MTD_Volume") and Year to Date ("YTD_Volume").

I have been able to generate a running total using the expression:

YTDVolume: DSum("[Volume]","[MyTable]","LogDate-1 < " & Format([LogDate],"\#yyyy-mm-dd\#"))

The part where I am stuck is that I cant seem to figure out what to do to make the running total reset every month (or year).

So just to clarify (if any is needed) this is the query I am trying to get
MyQuery:
ID LogDate Volume YTD_Volume MTD_Volume
1- 1/2/09-----10------- 10 ---------10
2- 1/5/09-----20--------30 ---------30
3- 6/15/09--- 15--------45----------15
4- 4/10/10--- 30--------30----------30
5- 4/20/10--- 25--------55----------55

I know that this is an task better suited for excel, but I have to do it in access and the problem is fairly straight forward. If anyone can help me it would be massively appreciated and I would be happy to contribute however I can in the future...

Thanks in advanced!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

strive4peace

Well-known Member
Joined
Jul 6, 2009
Messages
678
Hi Nebula (what is your name?)

Firstly, you should not use ID as a fieldname as it is ambiguous and not descriptive (maybe you just used this as an example)

try this:
Code:
MTDVolume: DSum("[Volume]"
,"[MyTable]"
,"Format(LogDate,'yyyymm') = '" &  Format([LogDate],'yyyymm') 
& "' AND [LogDate] < #" & [LogDate] & "#")
btw, you should do something along these lines for YTD as well since you have not taken different years into account.
 

nebula13

New Member
Joined
Nov 20, 2009
Messages
19
Crystal, you rock!
I took your code and very quickly got exactly what I was looking for. I ended up making a minor change so that the code that worked was:

MTD_Volume: DSum("[Volume]","[tbl_for_Lisa]","Format(LogDate,'yyyymm') = '" & Format([LogDate],'yyyymm') & "' AND [LogDate]-1 < #" & [LogDate] & "#")

Now all I have to do is figure out how to get running averages...

But this is 99.9% your contribution, so THANK YOU VERY MUCH :cool:

Much appreciation,
Ben
 

dallr

New Member
Joined
May 15, 2006
Messages
39
Crystal what are you doing on this neck of the woods? Smile

Dane
 

strive4peace

Well-known Member
Joined
Jul 6, 2009
Messages
678

ADVERTISEMENT

you're welcome, Ben ;)

"running averages" -- those are tough to do in Access. What I would suggest would be to add a Long Integer field that you assign by looping through a recordset in code. Then, you can more easily create and average with the number above and below. If you had information on every date, you wouldn't need to do this but it looks like you are missing some dates

Dane -- howdy! Nice to see you :)
 

strive4peace

Well-known Member
Joined
Jul 6, 2009
Messages
678
Hi Ben,

just had a thought ... if you are going to loop through the recordset and assign a sequential number, you might as well just store the calculated running averages instead ... otherwise, they will probably take some time to calculate on the fly.
 

dallr

New Member
Joined
May 15, 2006
Messages
39
You can do all what you are asking for including the running averages using pure SQL. Using the same fields you outlined in your first post and naming the table MyTable you can use the following SQL.


SELECT MyTable.ID, MyTable.LogDate, MyTable.Volume, (SELECT SUM(Volume) FROM MyTable AS X WHERE Format(X.LogDate,"mmyyyy") = Format(MyTable.LogDate,"mmyyyy") AND X.ID <=MyTable.ID) AS MTD, (SELECT SUM(Volume) FROM MyTable AS X WHERE Year(X.LogDate) = Year(MyTable.LogDate) AND X.ID <=MyTable.ID) AS YTD, (SELECT SUM(Volume)/Count(*) FROM MyTable AS X WHERE Format(X.LogDate,"mmyyyy") = Format(MyTable.LogDate,"mmyyyy") AND X.ID <=MyTable.ID) AS MTD_Avg
FROM MyTable;
 

dallr

New Member
Joined
May 15, 2006
Messages
39
Hey Crystal,
I pop in hear to have experts like Nate and his friends pull me out of my Excel jams.

Stay blessed!

Dane
 

Watch MrExcel Video

Forum statistics

Threads
1,130,081
Messages
5,639,955
Members
417,120
Latest member
Pavithra devi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top