Really Urgent DSUM Problem (1 Viewer)

I know that using DSum in queries is not a good idea but it is the only thing that will solve my problem.

I have 2 tables

Table 1 - Departments
Field 1 - Department Name
Field 2 - EACCode

Table 2 - EACStore
Field 1 EACCode
Field 2 Batch Date
Field 3 Actual Cost

The query I have is as follows
Field 1 Department Name - Criteria Limits this to One Department
Field 2 EACCode
Field 3 Batch Date - Criteria Limits this to a 12 Month Period
Field 4 Actual Cost

I need to create a running sum in a 5th Field as I need to use this calculation in the creation on a Graph.

I have used the running sum option in my tabular report, but I need a way to do this for my query.

Any help anyone can give me would be most appreciated. as this is the last part of the current project I am working on.

Thanks in advance
Geoff

Function RunningSum(Source As String, KeyName As String, KeyValue, _
FieldToSum As String)
'***********************************************************

' FUNCTION: RunSum()
' PURPOSE: Compute a running sum in a query.
' PARAMETERS:
' Source - The table containing the previous value to
' retrieve.
' KeyName - The name of the table unique key field.
' KeyValue - The current record's key value.
' FieldToSum - The name of the field in the previous
' record containing the value to retrieve.
' RETURNS: A running sum of the field FieldToSum.

' EXAMPLE: =RunSum("Table","ID",[ID],"Amount")
'***********************************************************
Dim rs As Recordset
Dim Result

On Error GoTo Err_RunningSum

' Get the form Recordset.
Set rs = CurrentDb().OpenRecordset((Source), dbOpenDynaset)

' Find the current record.
Select Case rs.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, _
DB_SINGLE, DB_DOUBLE, DB_BYTE

rs.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
GoTo Bye_RunningSum
End Select

' Compute the running sum.
Do Until rs.BOF
Result = Result + rs(FieldToSum)

' Move to the previous record.
rs.MovePrevious
Loop

Bye_RunningSum:
RunningSum = Result
Exit Function

Err_RunningSum:
Resume Bye_RunningSum

End Function

Rich,

I am completely confused where and how do I use this to create my Graph

Thanks
Geoff

Hi I tried using the following sql that i found in another thread but I keep on getting syntax problems any ideas

Thanks
Geoff

SELECT [Recharge All Sites (Current)].[Faculty Name], [Recharge All Sites (Current)].[Actual Batch Date], [Recharge All Sites (Current)].[Actual Cost], SUM([Recharge All Sites (Current)1].[Actual Cost])
FROM [Recharge All Sites (Current)], [Recharge All Sites (Current)] AS [Recharge All Sites (Current)1]
WHERE ((([Recharge All Sites (Current)].[Actual Batch Date])>=[Recharge All Sites (Current)1].[Actual Batch Date])
AND (([Recharge All Sites (Current)].[Faculty Name])=[Recharge All Sites (Current)1].[Faculty Name]))
GROUP BY [Recharge All Sites (Current)].[Faculty Name], [Recharge All Sites (Current)].[Actual Batch Date], [Recharge All Sites (Current)].[Actual Cost],

Come on guys I know there is someone out there who can help me with this. Please

Intervals, please

What's wrong with Rich's solution? You put this in a standard module and pass 4 arguments to it. These are described in the code's preamble.

Your source will have to come from a select query with a join between EACCode fields, rather than a table. Presumably the EACCode is the Key field,it's called EACCode and the FieldtoSum is TotalCost.

This gives a single output which is the cumulative total of all the total cost fields. This could also be found with a totals query which sums the field concerned. Rich's code uses a generic approach, but if you only want this one value you can use a simple totals query.

I don't call that a running sum by the way. It's a cumulative total, plain and simple. A running sum is a periodically increasing total from which you can, for example create a rising graph line. To create that, you will need to define intervals between periods under consideration. You haven't told us that.....

Rich, Help!

Rich,

Am having trouble trying to get the code to work for me, I have a query that pulls data together and I need to calculate a running total / sum to create a chart. The picture shows what the query data structure would be like.

I'll have Project which will have individual Tasks under that, then each Task will have a Week associated with it and a Cost for each week. I need to have a weekly running total for each Project/Task combo...

Any help would be greatly appreciated.

Geoff Codd did give a sample database that does that using a Val(DSum.... expression, which is fine but I also want to try out your code for fun and to see if it's simpler to apply. To see that thread
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=31806

Does anyone know if this code (assumng I understood it enough to use it) would be more efficient that using a DSum expression?

Why is using DSum in a query a bad idea?

No. It won't be more efficient; it will be less efficient.

Using a DSum() or the suggested function in a query is inefficient because of the requirement to re-read massive amounts of data. To understand the inefficiency you need to know how to program. To create a report or sub that calculates a running sum, you only need to sort your input file into the correct sequence before you start. You pass the recordset ONLY once. As you read each record, you add the current value to the running sum variable. If you are producing a report, you write the detail line, if you are producing a recordset, you write the current record and move on to the next record in the input recordset. You NEVER need to process the same record more than once.

The way DSum() and the sample function posted by Rich work, is that for each record of the input recordset, they re-read all the records of the recordset with a key lower than the current one and sum them. The amount of I/O required can be enormous if the recordset contains more than a few hundred rows. Here's a small example of how the processing goes:


As you can see, for a recordset of just 10 records, 45 extra reads are required. Not to mention the fact that each of these requires code to calculate 10 sums rather than just 1.

Don't get me wrong, DSum() and the other similar functions have their uses. Just NOT in a query or in the detail section of a report. Using them in a form is not such a problem since the form is working with only one record at a time. So although if you scrolled through the entire recordset one record at a time, you would effectively reproduce the effect of doing the DSum() in a query, no user is likely to page through thousands of records.

Wonderful explanation, Pat!

Pat,

As always, you have supplied a wonderful explanation to my question! Thank you!

Although, since I don't know any Access VBA, I guess I'll stick with using the DSum or Rich's function code (which I have yet to figure out BTW ).

If anyone out there knows of another way to create a running sum is Access, I'd appreciate you sharing it with me.

Maybe the data needs to be exported into MS Excel and have the graph/chart created in Excel? Can you import a graph/chart from Excel back into Access??

Make a report that included just the fields that you need in the detail section along with the running sum. No headers or field labels. Export the "report" to Excel.

Thanks, PAT!

Pat,

Thanks, will try that out.

Does anyone know if it is better to create a graph/chart within MS Access of to export the data to MS Excel and have Excel graph it?

If Excel is better, would it be a good idea to import the Graph back into Access, to include as part of a Report? Also, would that be an easy thing to do or would that most likely require some VBA code (which I don't know how to do, unless I could do that with a macro).

THANKS!

UPDATE: JonK has figured out a way to use the Select function instead of DSum to perform a Running Total over a group in a query!

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=31806&perpage=15&pagenumber=2