Running Sum 1

[astrevens]

Hi,
I need to have a running sum in "make table" query.
i have Value, cost and target i cant do a running sum in a report as i need to make graphs from this information. I dont know of a way to base a graph on a report.

Many thanks

 

[dawnd3]

What field are you trying to get a running sum on? All 3? Does this mean that for each record in the query you want it to add that record's value to the next record and so on? I know what a running sum is I just want to make sure I understand your question. I don't think you can do a running sum in a query, but I will check and make sure. Are you using Access 2000? Dawn

http://www.calliopeconsulting.net

 

[GingerR]

see answer in duplicate post in this forum.
i didnt see this post at first. didnt know what your fields were made of, just assumed the 'x-axis' data would be a date.
so now i have the same question - what is your x-axis value?

g

 

[BoxHead]

astrevens,

I have the following function in a module that I call whenever updates are made to the underlying table. It gives me the running sum, Total Sales.

Public Function RunningSum() As Currency
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSales")
RunningSum = 0
rs.MoveLast
rs.MoveFirst
For i = 1 To rs.RecordCount
  RunningSum = RunningSum + rs![SaleAmount]
  rs.Edit
  rs![TotalSales] = RunningSum
  rs.Update
  rs.MoveNext
Next i
End Function

I run the following on the AfterUpdate of the SaleAmount field on my input form to keep everything current.

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
RunningSum
TotalSales.Requery

HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[BoxHead]

GingerR,

I just saw your answer in the other post. Very nice.
John

Use what you have,
Learn what you can,
Create what you need.

 

[GingerR]

glad to help. i use this frequently.

also you can use Between too, i.e. for one-week rolling avg, but # are tricky (# indicates a date type):

dsum(".......","[ThingsDate] between #" & [ThingsDate] & "# and #" & [ThingsDate]-7 & "#&quot

have fun--g