P: n/a

Hello,
I am trying to create a running subtotal in a query and then graph the
running subtotal for each city group. The data looks like this in the
table (for illustration):
My City My Date Cumulative Sum Daily Sum
DC 03Apr07 6 6
DC 04Apr07 20 14
DC 05Apr07 0 0
DC 06Apr07 24 4
DC 09Apr07 26 2
DC 10Apr07 0 6
DC 11Apr07 32 44
Arlington 12Apr07 2 2
Arlington 13Apr07 0 0
Arlington 16Apr07 3 1
Arlington 17Apr07 0 0
The problem is that the cumulative sum goes to zero when the daily sum
is 0, so the graph is not cumulative. I need the cumulative sum to
never be 0, unless all previous values for the group are zero.
I was able to use DSum() to calculate a running sum, but it was over
the entire record set, not by city, so that did not work. I know a
report will work for this, too, but I need a query so I can graph the
values.
Any help is greatly appreciated! I thought this would be trivial, but
I cannot figure out how to do it.
Best,
Meb  
Share this Question
P: n/a
 me*******@yahoo.com wrote:
Hello,
I am trying to create a running subtotal in a query and then graph the
running subtotal for each city group. The data looks like this in the
table (for illustration):
My City My Date Cumulative Sum Daily Sum
DC 03Apr07 6 6
DC 04Apr07 20 14
DC 05Apr07 0 0
DC 06Apr07 24 4
DC 09Apr07 26 2
DC 10Apr07 0 6
DC 11Apr07 32 44
Arlington 12Apr07 2 2
Arlington 13Apr07 0 0
Arlington 16Apr07 3 1
Arlington 17Apr07 0 0
The problem is that the cumulative sum goes to zero when the daily sum
is 0, so the graph is not cumulative. I need the cumulative sum to
never be 0, unless all previous values for the group are zero.
I was able to use DSum() to calculate a running sum, but it was over
the entire record set, not by city, so that did not work. I know a
report will work for this, too, but I need a query so I can graph the
values.
Any help is greatly appreciated! I thought this would be trivial, but
I cannot figure out how to do it.
Best,
Meb
You aren't using Dsum correctly.
Cumulative Sum: Dsum("[Daily Sum]","TableName",
"City = '" & [My City] & "' And "
"My Date <= #" & [My Date])  
P: n/a

salad wrote:
me*******@yahoo.com wrote:
>Hello, I am trying to create a running subtotal in a query and then graph the running subtotal for each city group. The data looks like this in the table (for illustration):
My City My Date Cumulative Sum Daily Sum DC 03Apr07 6 6 DC 04Apr07 20 14 DC 05Apr07 0 0 DC 06Apr07 24 4 DC 09Apr07 26 2 DC 10Apr07 0 6 DC 11Apr07 32 44 Arlington 12Apr07 2 2 Arlington 13Apr07 0 0 Arlington 16Apr07 3 1 Arlington 17Apr07 0 0
The problem is that the cumulative sum goes to zero when the daily sum is 0, so the graph is not cumulative. I need the cumulative sum to never be 0, unless all previous values for the group are zero.
I was able to use DSum() to calculate a running sum, but it was over the entire record set, not by city, so that did not work. I know a report will work for this, too, but I need a query so I can graph the values.
Any help is greatly appreciated! I thought this would be trivial, but I cannot figure out how to do it.
Best, Meb
You aren't using Dsum correctly.
Cumulative Sum: Dsum("[Daily Sum]","TableName",
"City = '" & [My City] & "' And "
"My Date <= #" & [My Date])
That shoud be
"My Date <= #" & [My Date] & "#")  
P: n/a

On Apr 20, 11:28 am, salad <o...@vinegar.comwrote:
salad wrote:
mebrab...@yahoo.com wrote:
Hello,
I am trying to create a running subtotal in a query and then graph the
running subtotal for each city group. The data looks like this in the
table (for illustration):
My City My Date Cumulative Sum Daily Sum
DC 03Apr07 6 6
DC 04Apr07 20 14
DC 05Apr07 0 0
DC 06Apr07 24 4
DC 09Apr07 26 2
DC 10Apr07 0 6
DC 11Apr07 32 44
Arlington 12Apr07 2 2
Arlington 13Apr07 0 0
Arlington 16Apr07 3 1
Arlington 17Apr07 0 0
The problem is that the cumulative sum goes to zero when the daily sum
is 0, so the graph is not cumulative. I need the cumulative sum to
never be 0, unless all previous values for the group are zero.
I was able to use DSum() to calculate a running sum, but it was over
the entire record set, not by city, so that did not work. I know a
report will work for this, too, but I need a query so I can graph the
values.
Any help is greatly appreciated! I thought this would be trivial, but
I cannot figure out how to do it.
Best,
Meb
You aren't using Dsum correctly.
Cumulative Sum: Dsum("[Daily Sum]","TableName",
"City = '" & [My City] & "' And "
"My Date <= #" & [My Date])
That shoud be
"My Date <= #" & [My Date] & "#") Hide quoted text 
 Show quoted text 
Thanks for the direction. Am just learning that it matters for the
syntax whether the field is numeric or character string. I am still
using incorrect criteria. It looks like the critieria are just being
ignored, based on the response I am getting. This is what I have (it
returns just one value which is the total for all rows, rather than a
running subtotal for each [MyCity] group). I am unclear on whether or
not I need the first criteria (on MyPK), which is on a primary key
value that is unique and sequential for each row? I have tried
without this part of the criteria and get the same result.
Also unclear on whether date should be <= or >=? The dates are
arranged in ascending order, by [MyCity].
MySum: DSum("MySum","mytablename","[MyPK] <=" & [MyPK] And "[MyCity] =
'" & [MyCity] & "'" And "[MyDate] <= #" & [MyDate] & "#")
Thanks for the help,
meb  
P: n/a
 me*******@yahoo.com wrote:
On Apr 20, 11:28 am, salad <o...@vinegar.comwrote:
>>salad wrote:
>>>mebrab...@yahoo.com wrote:
>>>>Hello, I am trying to create a running subtotal in a query and then graph the running subtotal for each city group. The data looks like this in the table (for illustration):
>>>>My City My Date Cumulative Sum Daily Sum DC 03Apr07 6 6 DC 04Apr07 20 14 DC 05Apr07 0 0 DC 06Apr07 24 4 DC 09Apr07 26 2 DC 10Apr07 0 6 DC 11Apr07 32 44 Arlington 12Apr07 2 2 Arlington 13Apr07 0 0 Arlington 16Apr07 3 1 Arlington 17Apr07 0 0
>>>>The problem is that the cumulative sum goes to zero when the daily sum is 0, so the graph is not cumulative. I need the cumulative sum to never be 0, unless all previous values for the group are zero.
>>>>I was able to use DSum() to calculate a running sum, but it was over the entire record set, not by city, so that did not work. I know a report will work for this, too, but I need a query so I can graph the values.
>>>>Any help is greatly appreciated! I thought this would be trivial, but I cannot figure out how to do it.
>>>>Best, Meb
>>>You aren't using Dsum correctly. Cumulative Sum: Dsum("[Daily Sum]","TableName", "City = '" & [My City] & "' And " "My Date <= #" & [My Date])
That shoud be "My Date <= #" & [My Date] & "#") Hide quoted text 
 Show quoted text 
Thanks for the direction. Am just learning that it matters for the
syntax whether the field is numeric or character string. I am still
using incorrect criteria. It looks like the critieria are just being
ignored, based on the response I am getting. This is what I have (it
returns just one value which is the total for all rows, rather than a
running subtotal for each [MyCity] group). I am unclear on whether or
not I need the first criteria (on MyPK), which is on a primary key
value that is unique and sequential for each row? I have tried
without this part of the criteria and get the same result.
Also unclear on whether date should be <= or >=? The dates are
arranged in ascending order, by [MyCity].
MySum: DSum("MySum","mytablename","[MyPK] <=" & [MyPK] And "[MyCity] =
'" & [MyCity] & "'" And "[MyDate] <= #" & [MyDate] & "#")
Thanks for the help,
meb
Numbers get surrounded by nothing. Strings by ' or " depending. Dates
by #.
You need to contatenate PK with an &. And change the City. I don't see
a need for [] around some field names either.
DSum("MySum","mytablename","MyPK <=" & [MyPK] &
" And MyCity = '" & [MyCity] &
"' And MyDate <= #" & [MyDate] & "#")
To avoid word wrap I used 3 lines. You really need to look at your &
and how you concatenated the filter string.  
P: n/a

Meb,
What version of Access are you using? (please say Access 97)
If so there is a domain function builder wizard available that takes away
about 99% of the PITA of getting the syntax right. http://support.microsoft.com/kb/172183/enus
I'm quite sure that it only works as an addon in Access97.
<me*******@yahoo.comwrote in message
news:11**********************@e65g2000hsc.googlegr oups.com...
On Apr 20, 11:28 am, salad <o...@vinegar.comwrote:
>salad wrote:
mebrab...@yahoo.com wrote:
>Hello, I am trying to create a running subtotal in a query and then graph the running subtotal for each city group. The data looks like this in the table (for illustration):
>My City My Date Cumulative Sum Daily Sum DC 03Apr07 6 6 DC 04Apr07 20 14 DC 05Apr07 0 0 DC 06Apr07 24 4 DC 09Apr07 26 2 DC 10Apr07 0 6 DC 11Apr07 32 44 Arlington 12Apr07 2 2 Arlington 13Apr07 0 0 Arlington 16Apr07 3 1 Arlington 17Apr07 0 0
>The problem is that the cumulative sum goes to zero when the daily sum is 0, so the graph is not cumulative. I need the cumulative sum to never be 0, unless all previous values for the group are zero.
>I was able to use DSum() to calculate a running sum, but it was over the entire record set, not by city, so that did not work. I know a report will work for this, too, but I need a query so I can graph the values.
>Any help is greatly appreciated! I thought this would be trivial, but I cannot figure out how to do it.
>Best, Meb
You aren't using Dsum correctly.
Cumulative Sum: Dsum("[Daily Sum]","TableName",
"City = '" & [My City] & "' And "
"My Date <= #" & [My Date])
That shoud be "My Date <= #" & [My Date] & "#") Hide quoted text 
 Show quoted text 
Thanks for the direction. Am just learning that it matters for the
syntax whether the field is numeric or character string. I am still
using incorrect criteria. It looks like the critieria are just being
ignored, based on the response I am getting. This is what I have (it
returns just one value which is the total for all rows, rather than a
running subtotal for each [MyCity] group). I am unclear on whether or
not I need the first criteria (on MyPK), which is on a primary key
value that is unique and sequential for each row? I have tried
without this part of the criteria and get the same result.
Also unclear on whether date should be <= or >=? The dates are
arranged in ascending order, by [MyCity].
MySum: DSum("MySum","mytablename","[MyPK] <=" & [MyPK] And "[MyCity] =
'" & [MyCity] & "'" And "[MyDate] <= #" & [MyDate] & "#")
Thanks for the help,
meb   This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 3563
 replies: 5
 date asked: Apr 20 '07
