• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1059
  • Last Modified:

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.
Please help
 
0
makkw102598
Asked:
makkw102598
  • 4
  • 2
  • 2
  • +4
1 Solution
 
makkw102598Author Commented:
Adjusted points to 100
0
 
p_biggelaarCommented:
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
 
DedushkaCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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

0
 
wesleystewartCommented:
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
 
CHiLiNVLnCommented:
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
 
CHiLiNVLnCommented:
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
 
DedushkaCommented:
CHiLiNVLn,
Did you read my comment?
0
 
wesleystewartCommented:
CHiLiNVLn:

You really shouldn't post other people's comments as answers.  That is really bad form.

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

Wes
0
 
BrianWrenCommented:
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
 
makkw102598Author 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.
To answer p biggelaar's question,
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
 
DedushkaCommented:
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
 
dtburdickCommented:
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now