By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 2,928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Sum certain fields in access by VBA

P: 7
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
Share this Question
Share on Google+
11 Replies


Rabbit
Expert Mod 10K+
P: 12,366
You can do it in a report by using a running sum.
Oct 5 '12 #2

P: 7
sum will give total sum...i am needing sum step by step
Oct 5 '12 #3

P: 7
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

twinnyfo
Expert Mod 2.5K+
P: 3,284
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:

Expand|Select|Wrap|Line Numbers
  1. =DSum("[Score]","tblTableName","[ScoreKey] <= " & Me.txtScoreKey)
  2.  
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

Rabbit
Expert Mod 10K+
P: 12,366
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

P: 7
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

P: 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

P: 7
i am wanting every record of "result" be accumulation above
Oct 6 '12 #9

twinnyfo
Expert Mod 2.5K+
P: 3,284
Sam,

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

zmbd
Expert Mod 5K+
P: 5,397
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.
Oct 6 '12 #11

P: 7
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.