If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > easy SQL question: running total

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Cavalier King Charles
 
Join Date: Dec 2002
Location: Préverenges, Switzerland
Posts: 3,739
easy SQL question: running total

what's the best way to get a running sum in SQL?

data is of the form:

month, number
1, 100
2, 50
3, 50
4, 200
5, 100
etc.

the return i want is:
1, 100
2, 150
3, 200
4, 400
5, 500
etc.


izy
__________________
currently using SS 2008R2
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Location: Riga, Latvia
Posts: 36
An interesting question. I've also came upon the same problem for a couple of times, and I am not sure that my solution was the best -- so I'll post it here to get criticized, and hopefully finally we'll get to an optimal solution. Like russians say, "truth is being born in disputes"

I personally prefer doing everything inside a query rather than on a form, so the statement could look like
Quote:
select
_____tblSource.Field1,
_____tblSource.Field2,
_____Sum(DSum("[Field2]","[tblSource]","[Field1]<=" & [Field1])) AS RunningTotal
from
_____tblSource
group by
_____tblSource.Field1,
_____tblSource.Field2
order by
_____tblSource.field1;
Sample data that I used in tblSource is
Field1 Field2
1 100
2 50
... ...
7 25
etc.

The minus of this approach would be that the Field1 values must always be sequential and you cannot sort the records otherwise than by Field1.

However, on a form you can use a recordset and then address the .AbsolutePosition property of each record to obtain the current record position in a recordset. This allows relational approach to data but it limits running total usage to the form only, because if we store it in a table it immediately becomes non-relational, unless the table is never sorted otherwise -- but then again we get to approach #1...

Anyway, it seems to me that using recordset and vb is better in this case.

PS The _ marks are for readability, so just throw them away...
__________________
Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

Last edited by Virgo-Libra; 12-06-03 at 09:33.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Location: Riga, Latvia
Posts: 36
So, any comments?

Eagerly awaiting criticism... Since the first solution is slow and the second requires tons of coding...
__________________
Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Location: T.O.
Posts: 326
Does it have to be SQL? The only way I can even think to approach this is with code - either Public *cough* variable (or a tmp table to hold the sum) and user-defined function or through a form and Module-Private *ahem* variable.
__________________
All code ADO/ADOX unless otherwise specified.
Mike.
Reply With Quote
  #5 (permalink)  
Old
Purveyor of Discontent
 
Join Date: Mar 2003
Location: The Bottom of The Barrel
Posts: 6,102
What are you going to do with the datasource? You may be thinking to hard.. heh.. if you intend to use it for a report then life would be much easier doing the calculation after you've already pulled the dataset.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Location: T.O.
Posts: 326
I think Izy already knows the answer. I think he's just teasing us.
__________________
All code ADO/ADOX unless otherwise specified.
Mike.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Location: Riga, Latvia
Posts: 36
Quote:
Originally posted by HomerBoo
I think Izy already knows the answer. I think he's just teasing us.
I thought exactly the same, judging by his other posts

But, back to the topic. I've tried several alternative approaches to creating a running sum by SQL and it really does seem that, for an SQL expression to work, it is necessary to know how the data is sorted, which would make it non-relational. MSDN suggests the same BTW

So, with the second option... Anyone up to try to write some little code piece? I will try myself, just for fun, over this weekend. Intrigued to see what can I finally get to...

PS I know that you can do this in a REPORT, but that is not so interesting
__________________
Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.
Reply With Quote
  #8 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
yes it can be done with sql, and yes it requires a column to sequence on (that's obvious), and yes, it's fairly inefficient (because it uses a theta join where each row is joined to multiple rows)--
Code:
select t1.month, t1.[number], sum(t2.[number])
from runningtotal t1
 , runningtotal t2
where t1.month >= t2.month
group by  t1.month, t1.[number]
rudy
http://r937.com/
Reply With Quote
  #9 (permalink)  
Old
Purveyor of Discontent
 
Join Date: Mar 2003
Location: The Bottom of The Barrel
Posts: 6,102
Quote:
yes it can be done with sql, and yes it requires a column to sequence on (that's obvious), and yes, it's fairly inefficient (because it uses a theta join where each row is joined to multiple rows)--
Nerd.










*No seriously, that's good stuff to know, thanks.
Reply With Quote
  #10 (permalink)  
Old
Cavalier King Charles
 
Join Date: Dec 2002
Location: Préverenges, Switzerland
Posts: 3,739
no i wasn't teasing at all - i just didn't check this post for a few days (since virgo-libra's first reply).

memo to brain: don't post questions over the weekend!


this is a genuine problem.

my data is a touch more complex than in the original question.

tblData: month, number, product, company x 70,000 rows

50 companies
25 products in 4 groups defined by
tblPgrp: pGroup, product

data is 5 years history plus current year ( x 1,000 rows/month)

i need to report for 50 companies x 4 prodGroups x CurrentYearMonths with a running total which gets sent to msGraph.

i have HUMUNGOUS problems with the access:graph timing problems, and i need to run hundreds (not an exaggeration!) of doevents() to get the graphs out straight.

my current strategy is divide and conquer:
qry1: get current year group by month & product
qry2: on qry1 group and sum by pGroup, Company
then a couple of recordsets
with companyRst
with pGroupRst
make table from Qry2 (edited: izy) with running sum 'temp table helps msGraph timing.
'time out to accept that my running sum was clueless thrashing-around programming!
next pGoupRst
run the report to .SNP and save
next companyRst
e-mail all the .SNP

this takes 40 minutes to run up to the e-mail step and touching anything on the PC trashes the run = 40 minutes coffee break.

virgo-libra's first reply was too depressing: DSUM !
50 cos x 4 groups x 12 months = ???? ...actually, HowMany DSUM's ?

anyway, too many DSUMs on 70,000 rows.

so: yes, it needs to be SQL cos it doesn't pass through a report but goes to msGraph.

rudy's reply is already fascinating with the fabulous "theta join". wow. even if it doesn't work, the name is enough! i've got to try it.

i dont have my A machine in front of me right now. ??? is runningtotal understood by A?

thanks for the thoughts already expressed and any more that come out later.

izy
__________________
currently using SS 2008R2

Last edited by izyrider; 12-09-03 at 13:30.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 267
izy

Don't know if this will help you, but it is good to know it is there.

Look at this MS knowledge Base Article - 207626

http://support.microsoft.com/default...roduct=acc2000

S-
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Location: T.O.
Posts: 326
Gee Teddy,

I hope you're actually acquainted with Rudy, because to the casual observer, it appears like he has a solution and you just have sarcasm.
__________________
All code ADO/ADOX unless otherwise specified.
Mike.
Reply With Quote
  #13 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
didn't bother me

i took it as a compliment

Reply With Quote
  #14 (permalink)  
Old
Cavalier King Charles
 
Join Date: Dec 2002
Location: Préverenges, Switzerland
Posts: 3,739
i'm trying my theta join right now.

probably i'll muddle through, but where did t2 come from?

izy
__________________
currently using SS 2008R2

Last edited by izyrider; 12-10-03 at 02:06.
Reply With Quote
  #15 (permalink)  
Old
Cavalier King Charles
 
Join Date: Dec 2002
Location: Préverenges, Switzerland
Posts: 3,739
here is a half-code half-SQL idea that occurred to me while i was struggling with t2 in my theta join.

any comments?

particularly any comments on likely performance vs the famous theta join running with
a/ dumb .MDB backend over LAN
b/ mySQL backend running the famous theta join



using saved queries to make testing easy:

query1:
group by Month and sum(aNum)

query2:
SELECT Query1.Month, CalcRT([SumOfaNum]) AS RunningTotal
FROM Query1
ORDER BY Query1.Month

in a module:
global RunSoFar as double
public function CalcRT(anyDouble as double) as double
RunSoFar = RunSoFar + anyDouble
CalcRT = RunSoFar
end function

in a form:
private sub butGo_Click()
RunSoFar = 0
docmd.openquery ("Query2")
end sub

initially i had some odd results (sometimes getting the wrong [too high]answer for the first month, but with correct accumulation for subsequent months). any suggestions why?

after a compact & repair this error seems to have gone away. i just ran 20,000 test loops without error.

next step is some benchmarking on a large data set and an effort to either reproduce the error or convince myself it wont come back again.

izy
__________________
currently using SS 2008R2
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On