Running Sum Query of Ranked Data

[Jorist]

I have data the I need to total using a running sum. However I need this
running sum to total based upon the value of the field being totaled (Largest
to Smallest). No other fields are available to uniquely identify this ranking.

I am using the code below however when I look at the resulting table, I
noticed that in those instances where the value of [Field] was identical to
other fields..... they would all be totaled together for all of those records.

CumulativeTotal: (SELECT Sum([Field1]) FROM [Table1] AS [Self] WHERE
[Self].[Field1] >= [Table1].[Field1])

Here is an example of what is occuring along with the desired results.

Field 1 Running Sum Results Desired
12 12 12
9 21 21
7 28 28
3 34 31
3 34 34
2 36 36
1 40 37
1 40 38
1 40 39
1 40 40


I have one other field available that is unique to each value in Field1 but
there is no ranking to this field. Some values are greater or less than other
values both before and after each Field2 value.


Any help trying to resolve this would be greatly appreciated.

 

[vanderghast]

CumulativeTotal: (SELECT Sum([Field1]) FROM [Table1] AS [Self] WHERE
[Self].[Field1] > [Table1].[Field1] OR (Self.field1=table1.field1 AND
self.pk >= table1.pk))


where pk is the primarykey field, OR a field with no duplicated value.

Note that your >= became a >. It is 'simply' a matter to uniquely order
(rank) the records, or in case of =, the equality has to be broken through
the use of other field, something the primary key does very nicely.





Vanderghast, Access MVP

 

[Marshall Barton]

I have data the I need to total using a running sum. However I need this
running sum to total based upon the value of the field being totaled (Largest
to Smallest). No other fields are available to uniquely identify this ranking.

I am using the code below however when I look at the resulting table, I
noticed that in those instances where the value of [Field] was identical to
other fields..... they would all be totaled together for all of those records.

CumulativeTotal: (SELECT Sum([Field1]) FROM [Table1] AS [Self] WHERE
[Self].[Field1] >= [Table1].[Field1])

Here is an example of what is occuring along with the desired results.

Field 1 Running Sum Results Desired
12 12 12
9 21 21
7 28 28
3 34 31
3 34 34
2 36 36
1 40 37
1 40 38
1 40 39
1 40 40


I have one other field available that is unique to each value in Field1 but
there is no ranking to this field. Some values are greater or less than other
values both before and after each Field2 value.

That is impossible without a field (or set of fields) that
can be used to determine that the 12 record is first, 9 is
second, etc. In other words, you must be able to specify a
query with an Order By clause that produces the list in the
correct sequence.

Note that a table does not contain anything beyond the data
in its fields. I.e. there is nothing that "remembers" when
the records were added.

 

[Jorist]

Thanks for the quick reply. I was actually working on the same solution that
you proposed when you posted. However, I am running into a slightly different
problem with this solution. The query results leave the first CumulativeTotal
field value blank and the final value is never added. As such the results
look like this:


Field 1 Running Sum Results Desired
12 12
9 12 21
7 21 28
3 28 31
3 31 34
2 34 36
1 36 37
1 37 38
1 38 39
1 39 40


Any thoughts?

CumulativeTotal: (SELECT Sum([Field1]) FROM [Table1] AS [Self] WHERE
[Self].[Field1] > [Table1].[Field1] OR (Self.field1=table1.field1 AND
self.pk >= table1.pk))


where pk is the primarykey field, OR a field with no duplicated value.

Note that your >= became a >. It is 'simply' a matter to uniquely order
(rank) the records, or in case of =, the equality has to be broken through
the use of other field, something the primary key does very nicely.





Vanderghast, Access MVP

I have data the I need to total using a running sum. However I need this
running sum to total based upon the value of the field being totaled
(Largest
to Smallest). No other fields are available to uniquely identify this
ranking.

I am using the code below however when I look at the resulting table, I
noticed that in those instances where the value of [Field] was identical
to
other fields..... they would all be totaled together for all of those
records.

CumulativeTotal: (SELECT Sum([Field1]) FROM [Table1] AS [Self] WHERE
[Self].[Field1] >= [Table1].[Field1])

Here is an example of what is occuring along with the desired results.

Field 1 Running Sum Results Desired
12 12 12
9 21 21
7 28 28
3 34 31
3 34 34
2 36 36
1 40 37
1 40 38
1 40 39
1 40 40


I have one other field available that is unique to each value in Field1
but
there is no ranking to this field. Some values are greater or less than
other
values both before and after each Field2 value.


Any help trying to resolve this would be greatly appreciated.

 

[Jorist]

Vanderghast:
Thanks for the help. I found the solution using your post.

Here is the code I finally ended up with in case anyone else needs it.

CumulativeTotal: (SELECT Sum([Field1]) FROM [Table1] AS [Self] WHERE
[Self].[Field1]>[Table1].[Field1] OR ([Self].[Field1]=[Table1].[Field1] AND
[Self].[Field2] > [Table1].[Field2] ) OR ([Self].[Field1]=[Table1].[Field1]
AND [Self].[Field2] = [Table1].[Field2] ))


Table1 - is a query that I am using to help drive a second query which uses
the above code in one of the fields.
Field1 - is a summed value that is the primary sort value highest to lowest
Field2 - is a primary key value that is a secondary sort value highest to
lowest

The Field 2 is used to allow values that are the same in Field1 to be
uniquely counted. I hope this helps anyone else running into the same issue.
Thanks again.

Thanks for the quick reply. I was actually working on the same solution that
you proposed when you posted. However, I am running into a slightly different
problem with this solution. The query results leave the first CumulativeTotal
field value blank and the final value is never added. As such the results
look like this:


Field 1 Running Sum Results Desired
12 12
9 12 21
7 21 28
3 28 31
3 31 34
2 34 36
1 36 37
1 37 38
1 38 39
1 39 40


Any thoughts?

CumulativeTotal: (SELECT Sum([Field1]) FROM [Table1] AS [Self] WHERE
[Self].[Field1] > [Table1].[Field1] OR (Self.field1=table1.field1 AND
self.pk >= table1.pk))


where pk is the primarykey field, OR a field with no duplicated value.

Note that your >= became a >. It is 'simply' a matter to uniquely order
(rank) the records, or in case of =, the equality has to be broken through
the use of other field, something the primary key does very nicely.





Vanderghast, Access MVP

I have data the I need to total using a running sum. However I need this
running sum to total based upon the value of the field being totaled
(Largest
to Smallest). No other fields are available to uniquely identify this
ranking.

I am using the code below however when I look at the resulting table, I
noticed that in those instances where the value of [Field] was identical
to
other fields..... they would all be totaled together for all of those
records.

CumulativeTotal: (SELECT Sum([Field1]) FROM [Table1] AS [Self] WHERE
[Self].[Field1] >= [Table1].[Field1])

Here is an example of what is occuring along with the desired results.

Field 1 Running Sum Results Desired
12 12 12
9 21 21
7 28 28
3 34 31
3 34 34
2 36 36
1 40 37
1 40 38
1 40 39
1 40 40


I have one other field available that is unique to each value in Field1
but
there is no ranking to this field. Some values are greater or less than
other
values both before and after each Field2 value.


Any help trying to resolve this would be greatly appreciated.

 

[vanderghast]

The last comparison has to include the >= case,


WHERE
[Self].[Field1] > [Table1].[Field1]
OR (Self.field1=table1.field1
AND
self.pk >= table1.pk))


note that for the pk comparison, it is >=, not >. Indeed, in the end, the
record will be compared to itself, and to be included, its ordering-values
can only be equal to themselves (cannot be > than itself), so the = is
required, at the end of the complex criteria.


Vanderghast, Access MVP

 

[Jorist]

yep.... .my suggested answer was just another way of achieving what you
shared with me. I did not realize it at first but it utimately worked out to
be the exact same thing.

When I first coded your suggestion into the field I must of had a typo that
I did not catch and did not throw an error which is why I kept working on a
solution. However, after my last post I dropped my final OR and changed the
first OR's pk comparison to >= and it worked fine.

I said all of that so say your initial solution was exactly right and what I
needed..... Thanks again.