435,397 Members | 2,552 Online
Need help? Post your question and get tips & solutions from a community of 435,397 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
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 therunning subtotal for each city group. The data looks like this in thetable (for illustration):My City My Date Cumulative Sum Daily SumDC 03-Apr-07 6 6DC 04-Apr-07 20 14DC 05-Apr-07 0 0DC 06-Apr-07 24 4DC 09-Apr-07 26 2DC 10-Apr-07 0 6DC 11-Apr-07 32 44Arlington 12-Apr-07 2 2Arlington 13-Apr-07 0 0Arlington 16-Apr-07 3 1Arlington 17-Apr-07 0 0The problem is that the cumulative sum goes to zero when the daily sumis 0, so the graph is not cumulative. I need the cumulative sum tonever 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 overthe entire record set, not by city, so that did not work. I know areport will work for this, too, but I need a query so I can graph thevalues.Any help is greatly appreciated! I thought this would be trivial, butI 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 =? 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 >salad wrote: >>>mebrab...@yahoo.com wrote: >>>>Hello,I am trying to create a running subtotal in a query and then graph therunning subtotal for each city group. The data looks like this in thetable (for illustration): >>>>My City My Date Cumulative Sum Daily SumDC 03-Apr-07 6 6DC 04-Apr-07 20 14DC 05-Apr-07 0 0DC 06-Apr-07 24 4DC 09-Apr-07 26 2DC 10-Apr-07 0 6DC 11-Apr-07 32 44Arlington 12-Apr-07 2 2Arlington 13-Apr-07 0 0Arlington 16-Apr-07 3 1Arlington 17-Apr-07 0 0 >>>>The problem is that the cumulative sum goes to zero when the daily sumis 0, so the graph is not cumulative. I need the cumulative sum tonever 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 overthe entire record set, not by city, so that did not work. I know areport will work for this, too, but I need a query so I can graph thevalues. >>>>Any help is greatly appreciated! I thought this would be trivial, butI 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. salad wrote: mebrab...@yahoo.com wrote: >Hello,I am trying to create a running subtotal in a query and then graph therunning subtotal for each city group. The data looks like this in thetable (for illustration): >My City My Date Cumulative Sum Daily SumDC 03-Apr-07 6 6DC 04-Apr-07 20 14DC 05-Apr-07 0 0DC 06-Apr-07 24 4DC 09-Apr-07 26 2DC 10-Apr-07 0 6DC 11-Apr-07 32 44Arlington 12-Apr-07 2 2Arlington 13-Apr-07 0 0Arlington 16-Apr-07 3 1Arlington 17-Apr-07 0 0 >The problem is that the cumulative sum goes to zero when the daily sumis 0, so the graph is not cumulative. I need the cumulative sum tonever 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 overthe entire record set, not by city, so that did not work. I know areport will work for this, too, but I need a query so I can graph thevalues. >Any help is greatly appreciated! I thought this would be trivial, butI 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.