Ordering query for DSum

R

Rhabdo

Registered User.
Local time
Today, 14:11
Joined
Jun 4, 2014
Messages
28
Hi i am trying to run a cumulative total for each month spanning different years. unfortunately i cannot get the order correct for the DSum calculation as it orders by month only and i cannot figure out how to order by year and month. I thought about adding in a temporary sequential number and sorting by that but i cannot figure out how to do that either.

below is the output from my query, i need the cumulative total to run from the first month in 2013 which is 7 in this case and not from month 1 of 2014 as it is currently doing.

This is what is in my query to calculate the cumulative total.

CumTotal: Val(Format((DSum("[Monthly Total]","[q_WQ_Rainfall_Dates1]","[mMonth]<=" & [mMonth] & ""))))

Any adice/help would be great, thanks


yYear mMonth Monthly Total CumTotal
2013 7 45 1020
2013 8 101.8 1121.8
2013 9 44.5 1166.3
2013 10 139 1305.3
2013 11 169 1474.3
2013 12 27 1501.3
2014 1 0 0
2014 2 13 13
2014 3 62 75
2014 4 128.5 203.5
2014 5 569 772.5
2014 6 202.5 975
 
You need another criteria for the year as well.
 
I have tried that but cannot get the syntax/code right, can you help me out?
 
It is only giving me the overall total with the following:

CumTotal: Val(Format((DSum("[Monthly Total]","[q_WQ_Rainfall_Dates1]","[yYear]<=" & "[mMonth]<=" & [yYear] & [mMonth] & ""))))
 
Code: 
"[mMonth] <= " & [mMonth] & " AND [yYear] = " & [yYear]
 
Thanks vbaInet, its closer but not quite yet what i need. Its doing cumulative sums per year but i need overall cumulative sum ie from July 2013 to August 2014 (this are user specified via a form and change all the time)

Would appreciate further assistance

Cheers


yYear mMonth MonthlyTotal CumTotal
2013 7 45 45
2013 8 101.8 146.8
2013 9 44.5 191.3
2013 10 139 330.3
2013 11 169 499.3
2013 12 27 526.3
2014 1 0 0
2014 2 13 13
2014 3 62 75
2014 4 128.5 203.5
2014 5 569 772.5
2014 6 202.5 975
2014 7 186 1161
2014 8 57.5 1218.5
 
Just remove the year part:
Code: 
"[mMonth] <= " & [mMonth]
 
That's what i had originally but i need the cumulative total sequentially by year and month as below.

yYear mMonth Monthly Total CumTotal NEW
2013 7 45 1206 45
2013 8 101.8 1307.8 146.8
2013 9 44.5 1352.3 191.3
2013 10 139 1491.3 330.3
2013 11 169 1660.3 499.3
2013 12 27 1687.3 526.3
2014 1 0 0 526.3
2014 2 13 13 539.3
2014 3 62 75 601.3
2014 4 128.5 203.5 729.8
2014 5 569 7 72.5 1298.8
2014 6 202.5 975 1501.3
 
I don't understand what the problem is now? The code I gave you will do exactly what you have above if [q_WQ_Rainfall_Dates1] is a query sorted by year and month.
 
sorry i can't post an image of the data in the query. As it stands the code does a cumulative sum from month 1 to month 12, however month 1 is January 2014 but i need it to start at July 2013 as the first in the cumulative total.

What i am getting is this

Jul 1206
Aug 1307
Sep 1352
Oct 1491
Nov 1660
Dec 1687
Jan 0
Feb 13
Mar 75
Apr 203

What i need is
Jul 45
Aug 146
Sep 191
Oct 330
Nov 499
Dec 526
Jan 526
Feb 539
Mar 601
Apr 729
 
Sorry for the hassle but q_WQ_Rainfall_Dates is sorted ascending by year first then Month. The results of that query are below:

yYear mMonth Monthly Total
2013 7 45
2013 8 101.8
2013 9 44.5
2013 10 139
2013 11 169
2013 12 27
2014 1 0
2014 2 13
2014 3 62
2014 4 128.5
2014 5 569
2014 6 202.5
2014 7 186
2014 8 57.5

i then have the cumulative total query running off the above results with the following code for the calculation:
CumTotal: Val(Format((DSum("[Monthly Total]","[q_WQ_Rainfall_Dates1]","[mMonth] <= " & [mMonth]))))

Sorted by year and month again (or not) gives me a cumulative total from Jan 2014 to Dec 2013

This is still what i am getting

yYear mMonth Monthly Total CumTotal
2013 7 45 1206
2013 8 101.8 1365.3
2013 9 44.5 1409.8
2013 10 139 1548.8
2013 11 169 1717.8
2013 12 27 1744.8
2014 1 0 0
2014 2 13 13
2014 3 62 75
2014 4 128.5 203.5
2014 5 569 772.5
2014 6 202.5 975
2014 7 186 1206
2014 8 57.5 1365.3


Not sure if i am missing some sort elsewhere in the query? in properties?
 
Oh I understand your predicament. You need a field in this format:

2013 + 7 = 2020
2013 + 8 = 2021
.
.
.
2014 + 8 = 2032

Now you can perform a DSum() on it. By the way you don't need the Val() and Format() functions in there. Or what's your reason for using them?

Code: 
"[yYear]+[mMonth] <= " & [yYear]+[mMonth]
 
Sorry again for the confusion, that is along the lines of what i was originally thinking of doing with a ROW or sequential number but i could not find a way to do it.

Unfortunately what you suggest is not going to work:

2013 + 7 = 2020
2013 + 8 = 2021
2014 +1 = 2015 less than 2020 and hence before it, unless i am being really stupid here?

Any idea of the code to put a sequential number / row number into the previous query, then i can sort by that?

thanks
 
No, you're absolutely right. I didn't give it much thought. This should do it:

Code: 
"CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= " & CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear]))
 
Hi vbaInet,

Thanks seems like we on the right track here, but unfortunately i do not understand the syntax and there appears to be an error as it is displaying the code as well as the number See below). But i took out the first half of the code and it is now just giving me the numbehr which is what i need, not sure what the first half of the code was for?

Revised to > OrderNum: CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear]))

This is the original result
OrderNum
CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= 41281
CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= 41282
CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= 41283
CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= 41284
CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= 41285
CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= 41286
CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= 41640
CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= 41641
CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= 41642
CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= 41643
CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= 41644
CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= 41645
CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= 41646
CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= 41647
 
What I gave you should go directly in the DSum() function:
Code: 
DSum("[Monthly Total]","[q_WQ_Rainfall_Dates1]","CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])) <= " & CLng(DateValue(1 & '/' & [mMonth] & '/' & [yYear])))
 
Thanks that's working perfectly now.

Could you help me to only display the first 12 rows

Reason: input from the form is Year and Month and I want to graph 12 months worth of data. If the user selects May 2013 my query is set to run on >= 2013 and 5 (May) and hence displays all of 2014 monthly data. But now i want to limit the data to May 2013 to April 2014 (12 months).

Any pointers/suggestions
 
Sorry just worked it out, in the Query Properties Top Numbers
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom