Accumulated values in query?

[Steff_DK]

Is it possible to make a query that returns a field as an accumulated value of the previous posts, plus whatever value is saved in the post itself?

The table holds a date field and the value.

I want the query to return the result sorted by date like so:

Date, value, totalvalue
12/03/2005, 1, 1
13/03/2005, 3, 4
14/03/2005, 2, 6
...

Can someone help with me with the part that comes before “as totalvalue” in the SQL sentence?

Thanks

 

[RoyVidar]

I doubt it's very efficient on large tables, but something like this, perhaps?

select o.[date], o.[value], 
    (select sum(s.[value]) 
        from yourtable s
        where s.[date] <= o.[date]
    ) as totalvalue
from yourtable o
order by o.[date]

- note - not tested, included brackets on reserved words...

 

[Steff_DK]

It works as long as there are no posts with same date value...

Date, value, totalvalue
12/03/2005, 1, 4
12/03/2005, 3, 4
14/03/2005, 2, 6
...

Is there any way to add an extra criteria in "where s.[date] <= o.[date]" ?

There is a field in the table called [RRHrs] that could be used when [Date] are the same, but how???

Btw how do I add multiple ORDER BYs in SQL queries?

Like
1. order by [Name]
2. order by [Date]

 

[RoyVidar]

You should be able to something like this, I hope (again, untested)

select o.[date], o.[value], 
    (select sum(s.[value]) 
        from yourtable s
        where s.[date] <= o.[date]
        and s.RRHrs <= o.RRHrs
    ) as totalvalue
from yourtable o
order by o.[date], o.RRHrs

Hint, hint, when working in the query grid, feel free to add different stuff (as more fields to sort by), then switch to SQL view... lot to learn that way

If this table contains some records, you may want to experiment with using a function to do the summing, as this will probably not be very efficient (that is, if it works)

 

[Steff_DK]

This was exactly what I needed.

Thanx a million!!!

 

[RoyVidar]

Actually, I'm not convinced it's a good idea at all, anymore. I put together a quick test, and think perhaps such approaches should rely on only one sort field, cause, as seen by the where clause of the inner query, only the records where both fields are less than or equal to "eachother" are calculated. So if the order of the RRHrs field isn't in the same order as the date, it could produce the below sample of values

Date        value  RRHrs totalvalue
12/03/2005    1      2       1
12/03/2005,   3      3       4
14/03/2005,   2      1       2
14/03/2005,   1      4       7
16/03/2005,   3      1       5
16/03/2005,   2      2       8

so, you'll probably better off if you can order by only one field - sorry I didn't test before posting.

 

[Steff_DK]

You're right.

Sorting can mess it up, so be careful out there. Fixed it in my qry though...

Thanks!