Help With Running Total Query

[June7]

The module name is not really important for code to work but it can be changed in the Access (not VBA) navigation pane.



Since the expression =RunSum has an = sign, this indicates placement should be in a textbox on form.

I think [SomeField] is supposed to be the field to sum. Use of Nz() function is to assure a value is passed to the function.

[Njliven]

I got the accumulated totals to work with the following SQL I adapted. I attached this to a subform and connected the parent/child field (ProjectID), but it still gives an accumulated total for the whole table and not just per ProjectID. Can someone now help me fix this problem. I tried grouping on the ProjectID but I get an error telling me the ProjectID is not part of the aggregate function and will not run.

Here is the SQL I have.

SELECT T1.RecordID, T1.Month_Year, T1.Projected_Chrgs, T1.Actual_Chrgs, (SELECT Sum(tbl_Projected_Actual.Projected_Chrgs) AS CumProjected


FROM tbl_Projected_Actual


WHERE tbl_Projected_Actual.RecordID <= T1.RecordID) AS CumProjected, (SELECT Sum(tbl_Projected_Actual.Actual_Chrgs) AS CumActual FROM tbl_Projected_Actual WHERE tbl_Projected_Actual.RecordID <= T1.RecordID) AS CumActual, T1.ProjectID
FROM tbl_Projected_Actual AS T1;

[June7]

Running sum by group does complicate. The sum must start over when the ProjectID changes. Easily done in a report.

Do you need to see all groups in one output?

[Njliven]

No, I do not need to see all the groups at once. Only per projectID. The main information is in the main form and then all the related data in subform(s).
I got it to work great in the report, but not on the subform or in the graphs.

[June7]

I have never needed a running sum and trying to do in query without considering grouping is tough enough. I attempted option 2 in http://support.microsoft.com/kb/290136 but that is not working for me.

I am afraid this is over my head. Review another recent thread trying running sum https://www.accessforums.net/queries...tml#post239940

[Njliven]

Been playing with everything I can find on the Web on running totals in a group. Nothing seems to work for my situation. Any suggestions that might work would be greatly appreciated.

[June7]

I know it can be done on report. Accomplishing in query or on form is way too much for me.

[lfpm062010]

Let me ask the question again.

Did you try the SQL in Post #13? That should work.

[Njliven]

Yes, the query in post #13 works but brings back the wrong amounts, my end total on the first project, which should be correct give me a total of 6,776,500, when it should be only 300,000. It also does the running sum on the whole table. I have several projects in one table and need a running sum for each project. Which is not happening. I thought when the query was attached to a subform and the subform parent/child relationships were established it would do a running total by project, but it does not work that way. So I have been trying to figure out how to run it so it would do a running total on a group. I am also going to need a graph of these accumulated totals and this also needs to be per project. So I believe I need have the query group by the projectID.

[June7]

The DSum would need additional criteria for the projectID. This is what I attempted as explained by the MS article referenced in post 20.

CDbl(DSum("Actual_Chrgs","tbl_Projected_Actual","C Date([Month-Year]) <= #" & CDate([tbl_Projected_Actual].[Month-Year]) & "# AND projectID=" & [projectID]))

[Njliven]

The query runs but the calculated fields show #Error. When I clicked on one of the fields with the #Error. This message shows up.
Data type mismatch in criteria expression. Not sure where the mismatch is as I cannot seem to find one. In the table Month_Year is set as a date, Projected_Chrgs and Actual_Chrgs are set as double currency and the ProjectID field is a text field.

[lfpm062010]

The ProjectID is text field so you just need to quote around the ProjectID.

CDbl(DSum("Actual_Chrgs","tbl_Projected_Actual","C Date([Month-Year]) <= #" & CDate([tbl_Projected_Actual].[Month-Year]) & "# AND projectID='" & [projectID] & "'"))

Hope this will work for you.

[Njliven]

The things you miss when looking at something for too long. Thanks for all who helped out. It is finally working working.