Prev: Text and background color of selected items in listbox
Next: Problem with query
From: koturtle on 8 Apr 2010 10:57
I need to have a query that does the opposite of a running sum. I
would like to show a totals column that actually decreases with each
record. The first record would need to be the total of a field and it
would decrease as events happen (expire). The result of the query
will help me create a burn down chart.

Any ideas?

Thanks

KO
From: Marco Pagliero on 8 Apr 2010 13:17
On 8 Apr., 16:57, koturtle <kol...(a)gmail.com> wrote:
> I need to have a query that does the opposite of a running sum.  I
> would like to show a totals column that actually decreases with each
> record.  The first record would need to be the total of a field and it
> would decrease as events happen (expire).  The result of the query
> will help me create a burn down chart.
This is not easy to do in a query, as no real record can have the sum
of all records and it is difficult to tell one record which records in
the list are before itself. But if you know how to make a running sum
in a report, you just have to subtract the individual running sums
from the total, I think.

Anyway, let say the records have some field "Value" and are sorted
after some field "Sort"

select value, sort, dSum("[Value]", "Table") -
nz(dSum("[Value]","Table", "[Sort]<" & [Sort])) as NotRunSum from
Table order by [Sort]

Not tested

Greetings
Marco P

From: Salad on 8 Apr 2010 13:40
koturtle wrote:
> I need to have a query that does the opposite of a running sum. I
> would like to show a totals column that actually decreases with each
> record. The first record would need to be the total of a field and it
> would decrease as events happen (expire). The result of the query
> will help me create a burn down chart.
>
> Any ideas?
>
> Thanks
>
> KO

Let's say you had an ID field and 10 records in the table; ID 1..10. And
you have a field called Money.
MoneySum : dsum("Money","MoneyTable","ID <= " & [ID])

That will create a column called MoneySum in your query.

You could create another column
MoneyTotalSum : dsum("Money","MoneyTable")

You could then create a third column
MoneyRunningSum : [MoneyTotalSum] - [MoneySum]

I'd do something different if a form or report.



From: koturtle on 8 Apr 2010 14:23
On Apr 8, 10:40 am, Salad <sa...(a)oilandvinegar.com> wrote:
> koturtle wrote:
> > I need to have a query that does the opposite of a running sum.  I
> > would like to show a totals column that actually decreases with each
> > record.  The first record would need to be the total of a field and it
> > would decrease as events happen (expire).  The result of the query
> > will help me create a burn down chart.
>
> > Any ideas?
>
> > Thanks
>
> > KO
>
> Let's say you had an ID field and 10 records in the table; ID 1..10. And
> you have a field called Money.
>         MoneySum : dsum("Money","MoneyTable","ID <= " & [ID])
>
> That will create a column called MoneySum in your query.
>
> You could create another column
>         MoneyTotalSum : dsum("Money","MoneyTable")
>
> You could then create a third column
>         MoneyRunningSum : [MoneyTotalSum] - [MoneySum]
>
> I'd do something different if a form or report.

Thank You, Thank You!!!!
 | 
Pages: 1
Prev: Text and background color of selected items in listbox
Next: Problem with query