Query - accumulated

[Guest]

hi

I have a query whith Col-A and Col-B. How can I do a accumulated(values
Col-B) in Col-C ?

Col-A Col-B Col-C (accumulated)
xpto(1st row) 5 5
abc 23 28
vxz 12 40
dff 3 43
....


thanks

 

[Allen Browne]

You must have some way to determine the order of the rows, so I will assume
you have an AutoNumber field named ID, primary key, and so C should be the
sum of values in this and preceeding rows.

You could type this into the Field row in your query:
C: DSum("B", "Table1", "ID <= " & [ID])

That will work, but will not be fast. If you do not mind read-only results,
a subquery like this would be much quicker:
C: (SELECT Sum(B) FROM Table1 AS Dupe WHERE Dupe.ID <= Table1.ID)

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

BTW, an even easier solution would be to do this in a report rather than a
query. Just add your B column to the report twice, and in the second text
box, set its Running Sum property.

 

[Guest]

thanks ... perfect!
--
Sérgio Aires
Lisboa
Portugal


"Sérgio Aires" escreveu: