# Cumulative calculation

In a Query.
I have some num in a field
say:
Number1   Number2    results Cumulative
2             2          4      4
2            3           5      9 (4+5)
0            0           0      9 (0+9)
The field Cumulative is the problem.

LVL 1
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Author Commented:
0
Commented:
If you want to do it in a query, it'll become quite difficult (if not impossible). There are some other way's to get the desired result (like opening the recordset, where you left room for the cummulative result, and calculate the result in code). Please let me know whether such a solotion is an option or not, and why/why not.
0
Commented:
Hi makkw,

One of easy ways to get cumulative sums is to create a report based on your query:
SELECT Number1, Number2 FROM tblNumbers

1. Locate on detail section of the report:
- Number1 and Number2 textboxes bounded on your query fields
- unbounded Result textbox and set it to:
= [Number1] + [Number2]
- unbounded Cumulative textbox and set it to:
= [Result]
and set its "Running Sum" property to "Over Group"

That's all.
Best regards,
Dedushka
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Sorry, must be:
and set its "Running Sum" property to "Over Group" or "Over All"

0
Commented:
makkw:

What exactly is the problem?  Can you post the SQL statement from your query here?

A query showing two values and their sum in a third field isn't very difficult and should be easy to fix.

Wes
0
Commented:
Using a query would not be easy to successfully accomplish, but in fact, nearly impossible.  Using a SQL statement would make it much easier if you understand how to go about doing that.

1. Locate on detail section of the report:
- Num1 and Num2

Find the Result textbox and set it to:
= [Num1] + [Num2]
Find the Cumulative textbox and set it to:
= [Sum]
and set its "Running Sum" property to "Over Group"

0
Commented:
Using a query would not be easy to successfully accomplish, but in fact, nearly impossible.  Using a SQL statement would make it much easier if you understand how to go about doing that.

1. Locate on detail section of the report:
- Num1 and Num2

Find the Result textbox and set it to:
= [Num1] + [Num2]
Find the Cumulative textbox and set it to:
= [Sum]
and set its "Running Sum" property to "Over Group"

0
Commented:
CHiLiNVLn,
0
Commented:
CHiLiNVLn:

makkw:
Please don't accept the answer that CHiLiNVLn proposed.  It is just a copy of Dedushka's comment.

Wes
0
Commented:
Makkw,

Please reject CHiLiNVLn's proposed 'answer.'  Help us keep this site operating in a mature, considerate, fashion alert.

You could try this:

--------------------

Public Function RunningSum(Optional fldA As Integer, Optional fldB As Integer) As Integer

Static RnSum as Integer

If IsMissing(fldA) Then
RnSum = 0 ' Allows resetting.
Else
RnSum = RnSum + Nz(fldA) + Nz(fldB)
EndIf

RunningSum = RnSum

End Function

--------------------

In a Query:

--------------------

SELECT A, B, RunningSum(tbl.A, tbl.B) AS [Balance]
FROM [tbl];

--------------------

In a macro, put two entries:

RunCode: RunningSum()
OpenQuery: <qryWhatEver>

Of course, the 'openquery' action could be carried out by the query being run to open a report, or form, etc.

You would put the number of arguments into the function as fits your needs.

Brian
0
Author Commented:
I am sorry, the problem I mentioned wasn't clear enough.
My objective is to try to have a 'form' eventually, which can do the calculation and put the updated calculation on to the table. Therefore the problem is in two parts:
1. how to calculate the runnning sum?
As I have mentioned before.
2. How to put the calculated values ( the running sums) back onto the table.
THe calcultion can be done in Queries or forms and/or module as well.
it is possible to have done the cumulative things in the recordset, but then I can't change the value or even not able to input any values.
Thanks
0
Commented:
makkw,
you can open a recordset in code and step over all records recalculating values you needed, but it is no good practice to store calculated values in the table. Generally in relational databases you should not store them, but calculate every time you need this values.
Regards,
Dedushka
0
Commented:
Anyone reading this might try the way I did it using a correlated subquery.  It is very fast.

In my case I needed to add the number of registrants to a conference and keep a running sum by day.

Here is the query:

SELECT a1.RegistrationDate, (SELECT COUNT(*) FROM Attendees AS a2 where a2.RegistrationDate <= a1.RegistrationDate) AS RunningSum
FROM Attendees AS a1
ORDER BY a1.RegistrationDate;
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.