1

Topic: The accumulative total

Hello.
Prompt, there is a table with coefficients. It is necessary to arrange at first the table on coefficients from  to smaller (it easily becomes in Access) and further in a following column it is necessary to calculate the accumulative total. Where the first cell will be accordingly equal to the first cell of coefficients and further is added on 1 line.
The initial table
Coefficient
5
7
8
4
2
1
9
6
3
The arranged table
Coefficient
9
8
7
6
5
4
3
2
1
The accumulative total
Coefficient, the Accumulative total
9,9
8,17
7,24
6,30
5,35
4,39
3,42
2,44
1,45

2

Re: The accumulative total

Im_Max;
To store in the table calculated values is not correctly, you "" is elementarily implemented in the report (property the total with accumulation) or hardly more difficult in request (there is a copy of the table with descending sort order and values less containing in current record of the table sorted by increase (sorting of tables are added can be , depending on desirable result)

3

Re: The accumulative total

sdku;
This column is necessary to me further for calculations. Therefore a variant it to make in the report does not arrange.
About the second variant not absolutely understood, it is possible a little more in detail?

4

Re: The accumulative total

Im_Max;

SELECT t., dsum ("[coefficient]", "[Koef]", "[coefficient]> =" and t.) as [accumulative coefficient]
FROM Koef as t
ORDER BY t. DESC;

5

Re: The accumulative total

Here is how changed the code request Access

SELECT t., DSum ("[coefficient]", "[Koef]", "[coefficient]> =" and [t]. [Coefficient]) AS [accumulative coefficient]
FROM Koef AS t
ORDER BY t. DESC;

Error produces as a result:
[img=https://disk.yandex.ru/client/disk?display=normal&groupBy=none&order=1&sort=name&view=icons&wasAsideAnimated=false&typeClustering=geo&action=null&idAlbum=undefined&idApp=client&dialog=slider&idDialog=%2Fdisk%2Fsql1.png]

6

Re: The accumulative total

Corrected syntax. There was a calculation. The line in request looks so:
Accumulative coefficient: DSum ("[coefficient]"; "[Koef]"; "[coefficient]> =" and ' [coefficient] ')
But the result is the total of all coefficients for every line.

7

Re: The accumulative total

8

Re: The accumulative total

Im_Max;
It is possible to go on other way. By a cycle to walk under the table and update to put down the totals

9

Re: The accumulative total

Tarasios;
In 90 % I work in a window of a plotter of requests. I can simple SQL write.
I so understood it is possible a few Dsum to correct, what all correctly would work?

10

Re: The accumulative total

-I ask  me-still easier than I wrote (I apologize-not attentively read)
-"Expression" in request not mandatory (it is entered that it was more convenient to check up request operation)
-To store a field accumulation not follows-it surplus information (create function and use it for the further calculations. about calculated fields )

11

Re: The accumulative total

After:

DSum ("[coefficient]"; "[Koef]"; "[coefficient]> =">> ' <<<[coefficient]>>> ' <<<)

your error-opostrofy (they should not be)

12

Re: The accumulative total

sdku;
About  at this forum found it. THERE the similar problem was with syntax. Very much the other way without them there is an error about which I wrote above. And with them there is a calculation, simply erratic, the accumulative total instead of the total on all coefficients is necessary.

13

Re: The accumulative total

sdku wrote:

After:

DSum ("[coefficient]"; "[Koef]"; "[coefficient]> =">> ' <<<[coefficient]>>> ' <<<)

your error-opostrofy (they should not be)

I will assort tomorrow yours an example, many thanks, I think all should turn out

14

Re: The accumulative total

sdku;
Made all as in your example. All the same produces an error. Began to understand. Unique difference of our requests in that that at you in an example coefficient is Long integer, in the post I too for an example specified that the coefficient is long integer. Actually at me coefficients this Double with a floating point. There small enough values with a heap of signs after a comma.
And so, I changed in your table a data format of Koef on Double with a floating point. Interposed there values from the table and the request began to produce an error too. It turns out Dsum does not work with "Double with a floating point"?

15

Re: The accumulative total

Basically transformed to long integer. And then the formula works, only 1 it is necessary to correct.
There is an accumulative total since the most smaller. And it is necessary for me on the contrary, from the biggest.
As now
, Accumulative
1,1
2,3
3,6
4,10
5,15
6,21
7,28
8,36
9,45
As it is necessary:
, Accumulative
9,9
8,17
7,24
6,30
5,35
4,39
3,42
2,44
1,45

16

Re: The accumulative total

All earned. Only here faced that it very long works. So that it not probably to use.
As a result that I wanted to make all, it to find the accumulative total (it at me is anchored to the goods, can because of them so long began to work). And at achievement of this total of certain value at me to the goods the category is appropriated.
The goods have a share. I need to arrange the goods on decrease of this share, further to find the goods at which  a share makes 80 % from the general share on all goods. It  the analysis.
As the accumulative total for each goods is considered very long, whether it is possible to find the goods on which the share  became equal 80 % from the general share. I.e. not to calculate shares for each goods, and to find the boundary goods, and accordingly all goods from shares above the found will have one category.
It can be made in Access?

17

Re: The accumulative total

SELECT t1. Coefficient, SUM (t2. Coefficient) the Total
FROM Coefficients t1, Coefficients t2
WHERE t1. Coefficient <= t2. Coefficient
GROUP BY t1. Coefficient

18

Re: The accumulative total

Im_Max wrote:

All earned. Only here faced that it very long works...

Domain functions absolutely not fast (on data bulks it noticeably is felt), but they also are easily replaced with appropriate subqueries smile

SELECT t., (select sum ([coefficient]) from [Koef] where [coefficient]> =t.) AS [the accumulative total]
FROM Koef AS t
ORDER BY t. DESC;