Sum certain fields in access by VBA

[sam71999]

I have table
Name score
A 20
B 40
c 10
d 70

I want create Form
Name Score Total
A 20 20
B 40 60
C 10 70
D 70 140

[Rabbit]

You can do it in a report by using a running sum.

[sam71999]

sum will give total sum...i am needing sum step by step

[sam71999]

name score result
x 10 10 ( x+0)
y 20 30(10+20)
z 40 70(30+40)
a 100 170(70+100)

[twinnyfo]

Sam,

If you had all unique names (or if all the names had a key identifying them in the example below) you could have a text box with the following:

=DSum("[Score]","tblTableName","[ScoreKey] <= " & Me.txtScoreKey)

This would add all the Score from the current record and all the records that fell previous to the current record.

Hope this helps.

[Rabbit]

I'm not talking about a sum, I'm talking about a running sum, which will give you a "step by step" as you call it. But that's for a report, if you need it for a form, you would use twinny's method.

[sam71999]

thank u for my helping
But if i need
x 10 10 ( x+0)
x 20 30(10+20)
x 40 70(30+40)
x 100 170(70+100)

[sam71999]

i changed table name to "tblTableNa me" and filed to ScoreKey and Score but it is not working

when changing to

=DSum("[Score]","tblTableName","[ScoreKey] <='x'")

all record under result give me 10

[sam71999]

i am wanting every record of "result" be accumulation above

[twinnyfo]

Sam,

If the data in your table is:

 x 10  10 ( x+0)
 x 20  30 (10+20)
 x 40  70 (30+40)
 x 100 170(70+100)

Then, no, my method in post #5 will not work. You need to have a primary key established for each record. In post #4, above, you listed your data as:

 x 10  10 ( x+0)
 y 20  30 (10+20)
 z 40  70 (30+40)
 a 100 170(70+100)

Which, although each record is unique and would work (if sorted a-z), the letters a-z could hardly be considered a primary key, unless your table was GUARANTEED to have fewer than 26 records, which is highly unlikely in almost any database.

What I mean by a primary key is a unique record identifier, which means nothing to the record except to spcifically point to that particular recrod. For example, your table with a primary key could look like this:

1001 x  10  10 (10)
1002 y  20  30 (10+20)
1003 z  40  70 (30+40)
1004 a 100 170 (70+100)

Then, whenever you refer to record 1002, you can pull these values from that specific:

 z  40

Then, in my example in post #5, the name of that primary key field would be ScoreKey, but you could call it anything. The only thing you would have to keep in mind is that you would have to sort your records by that primary key.

There are alternative ways to create a query in which you can sort by another column, and then establish a rank ordering, but the concept is the same once you get the data in order. You use the field you are sorting on (which must be a unique value) and use the method in post #5.

You may need to post your table and form on this forum for us to understand what is not working properly....

[zmbd]

Sam;
Please show us your work.
This is very simular to an intro to access running totals homework problem.
You've also appeared to have driffted from your OP.

[sam71999]

thank You very much...i understand u lesson