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!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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
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,099,353
Messages
5,468,116
Members
406,566
Latest member
MsfStl

This Week's Hot Topics

Top