Acumulated column

[Guest]

Access Query
I have a query with two columns: ItemCode and Value.
I need to create a third column accumulating the preceding values on the
column Value. Put it in another way I need it to sum the value in preceding
row with the value of the column Value in the same row.
More or less like this:
ITEMCODE Value Acumulated
RE1 4 4
RE2 -6 -2
RE3 3 1
Is it possible? Can anyone tell me how? I have no idea to solve this.

 

[Allen Browne]

Assuming that the ITEMCODE defines the sort order, you can use a subquery to
give you the accumlated total.

Type something like this into the Field row in query design:
Accumulated: (SELECT Sum(Value) AS SumOfValue
FROM Table1 AS Dupe
WHERE Dupe.ItemCode <= Table1.Dupe)

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Guest]

Hi Raul Sousa,

If you change your design of the table. It can be done easier.
For example, if you table is like

Table1:
(Number) (Number) (Number)
ITEMCODE Value Acumulated
1 4 4
2 -6 -2
3 3 1

Then the query will be

SELECT [Table1].ITEMCODE, DSum("Value","[Table1]","ITEMCODE<=" & [ITEMCODE])
AS Acumulated
FROM [Table1];

Hope this helps.

 

[Guest]

Thank you, this just gave me the solution that I was searching for 2 weeks.