Question Re: Monthly Running Sum query

[nebula13]

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!

 

[strive4peace]

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:

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]

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

Much appreciation,
Ben

 

[dallr]

Crystal what are you doing on this neck of the woods? Smile

Dane

 

[strive4peace]

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]

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]

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]

Hey Crystal,
I pop in hear to have experts like Nate and his friends pull me out of my Excel jams.

Stay blessed!

Dane