create a running subtotal in a query and then graph results

[mebrabham]

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

[salad]

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])

[salad]

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] & "#")

[mebrabham]

On Apr 20, 11:28 am, salad <[e-mail]:

salad wrote:

[e-mail] 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

[salad]

me*******@yahoo.com wrote:

On Apr 20, 11:28 am, salad <[e-mail]:

>>salad wrote:

>>>[e-mail] 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.

[Don Leverton]

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 <[e-mail]:

>salad wrote:

[e-mail] 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