By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,644 Members | 2,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,644 IT Pros & Developers. It's quick & easy.

create a running subtotal in a query and then graph results

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 03-Apr-07 6 6
DC 04-Apr-07 20 14
DC 05-Apr-07 0 0
DC 06-Apr-07 24 4
DC 09-Apr-07 26 2
DC 10-Apr-07 0 6
DC 11-Apr-07 32 44
Arlington 12-Apr-07 2 2
Arlington 13-Apr-07 0 0
Arlington 16-Apr-07 3 1
Arlington 17-Apr-07 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

Apr 20 '07 #1
Share this Question
Share on Google+
5 Replies


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 03-Apr-07 6 6
DC 04-Apr-07 20 14
DC 05-Apr-07 0 0
DC 06-Apr-07 24 4
DC 09-Apr-07 26 2
DC 10-Apr-07 0 6
DC 11-Apr-07 32 44
Arlington 12-Apr-07 2 2
Arlington 13-Apr-07 0 0
Arlington 16-Apr-07 3 1
Arlington 17-Apr-07 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])
Apr 20 '07 #2

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 03-Apr-07 6 6
DC 04-Apr-07 20 14
DC 05-Apr-07 0 0
DC 06-Apr-07 24 4
DC 09-Apr-07 26 2
DC 10-Apr-07 0 6
DC 11-Apr-07 32 44
Arlington 12-Apr-07 2 2
Arlington 13-Apr-07 0 0
Arlington 16-Apr-07 3 1
Arlington 17-Apr-07 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] & "#")
Apr 20 '07 #3

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 03-Apr-07 6 6
DC 04-Apr-07 20 14
DC 05-Apr-07 0 0
DC 06-Apr-07 24 4
DC 09-Apr-07 26 2
DC 10-Apr-07 0 6
DC 11-Apr-07 32 44
Arlington 12-Apr-07 2 2
Arlington 13-Apr-07 0 0
Arlington 16-Apr-07 3 1
Arlington 17-Apr-07 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

Apr 20 '07 #4

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 03-Apr-07 6 6
DC 04-Apr-07 20 14
DC 05-Apr-07 0 0
DC 06-Apr-07 24 4
DC 09-Apr-07 26 2
DC 10-Apr-07 0 6
DC 11-Apr-07 32 44
Arlington 12-Apr-07 2 2
Arlington 13-Apr-07 0 0
Arlington 16-Apr-07 3 1
Arlington 17-Apr-07 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.
Apr 21 '07 #5

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/en-us

I'm quite sure that it only works as an add-on 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 03-Apr-07 6 6
DC 04-Apr-07 20 14
DC 05-Apr-07 0 0
DC 06-Apr-07 24 4
DC 09-Apr-07 26 2
DC 10-Apr-07 0 6
DC 11-Apr-07 32 44
Arlington 12-Apr-07 2 2
Arlington 13-Apr-07 0 0
Arlington 16-Apr-07 3 1
Arlington 17-Apr-07 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

Apr 23 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.