469,126 Members | 1,261 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

Sum certain fields in access by VBA

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
Oct 5 '12 #1
11 4317
12,516 Expert Mod 8TB
You can do it in a report by using a running sum.
Oct 5 '12 #2
sum will give total sum...i am needing sum step by step
Oct 5 '12 #3
name score result
x 10 10 ( x+0)
y 20 30(10+20)
z 40 70(30+40)
a 100 170(70+100)
Oct 5 '12 #4
3,653 Expert Mod 2GB

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:

Expand|Select|Wrap|Line Numbers
  1. =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.
Oct 5 '12 #5
12,516 Expert Mod 8TB
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.
Oct 5 '12 #6
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)
Oct 6 '12 #7
i changed table name to "tblTableName" and filed to ScoreKey and Score but it is not working

when changing to
Expand|Select|Wrap|Line Numbers
  1. =DSum("[Score]","tblTableName","[ScoreKey] <='x'")
all record under result give me 10
Oct 6 '12 #8
i am wanting every record of "result" be accumulation above
Oct 6 '12 #9
3,653 Expert Mod 2GB

If the data in your table is:

Expand|Select|Wrap|Line Numbers
  1.  x 10  10 ( x+0)
  2.  x 20  30 (10+20)
  3.  x 40  70 (30+40)
  4.  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:

Expand|Select|Wrap|Line Numbers
  1.  x 10  10 ( x+0)
  2.  y 20  30 (10+20)
  3.  z 40  70 (30+40)
  4.  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:

Expand|Select|Wrap|Line Numbers
  1. 1001 x  10  10 (10)
  2. 1002 y  20  30 (10+20)
  3. 1003 z  40  70 (30+40)
  4. 1004 a 100 170 (70+100)
Then, whenever you refer to record 1002, you can pull these values from that specific:

Expand|Select|Wrap|Line Numbers
  1.  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....
Oct 6 '12 #10
5,400 Expert Mod 4TB
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.
Oct 6 '12 #11
thank You very much...i understand u lesson
Oct 7 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Chumley the Walrus | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.