sum of a data field

S

stevesjohn2486

I have field named [strGuidance]: there are 3 possiblities VI ,CT, and US. I
would like a sum of each at the end of report? is this possible? I can get
the total count but would like to break it down. How would I do this?
Thanks
steve j
 
S

Steve Schapel

Steve,

It is not clear what you want. Do you mean that the VI, CT, and US
possibilities are the data in the strGuidance field? So, this is text,
not numerical, so you can't get a sum of this field. Do you mean the
sum of another field for each of the 3 groups according to the value of
strGuidance? Maybe an example would help to convey your meaning.
 
S

stevesjohn2486

Yes. the vi,ct,and us are the only entries possible. I would like have a
total count of the number of each ct's, vi's and us's. at the end of my
monthly report.


Steve Schapel said:
Steve,

It is not clear what you want. Do you mean that the VI, CT, and US
possibilities are the data in the strGuidance field? So, this is text,
not numerical, so you can't get a sum of this field. Do you mean the
sum of another field for each of the 3 groups according to the value of
strGuidance? Maybe an example would help to convey your meaning.

--
Steve Schapel, Microsoft Access MVP


I have field named [strGuidance]: there are 3 possiblities VI ,CT, and US. I
would like a sum of each at the end of report? is this possible? I can get
the total count but would like to break it down. How would I do this?
Thanks
steve j
Click to expand...
Click to expand...
 
D

Dodo

I have field named [strGuidance]: there are 3 possiblities VI ,CT, and
US. I would like a sum of each at the end of report? is this possible?
I can get the total count but would like to break it down. How would I
do this? Thanks
steve j
Click to expand...

Put 3 fields at the end of the report like:

=DCount("[strGuidance]";"YourTableOrQueryName";"[strGuidance] = 'VI'")
=DCount("[strGuidance]";"YourTableOrQueryName";"[strGuidance] = 'CT'")
=DCount("[strGuidance]";"YourTableOrQueryName";"[strGuidance] = 'US'")

I'm using a Dutch version of Access so you might have to change the
separator.
 
K

Ken Sheridan

Steve:

Add three text boxes to the Report Footer with ControlSource properties as
follows:

=Sum(IIf([strGuidance] = "VI",1,0))
=Sum(IIf([strGuidance] = "CT",1,0))
=Sum(IIf([strGuidance] = "US",1,0))

By summing the return values of the IIf function (1 or 0) you are in effect
counting the instances of each value. You might see the following method
recommended sometimes:

=Sum(Abs([strGuidance] = "VI"))
or
=Sum(([strGuidance] = "VI")*-1)

DON'T DO THIS! Its making use of the implementation of Boolean (True/False)
values in Access as -1 (True) and 0(False). To make use of the implementaion
in this way is bad programming practice.
 
S

Steve Schapel

Steve,

Ok, now I understand :) Count not Sum.

So, the reply from Ken Sheridan provides some ways of doing this. Of
his suggestions, the one I use is...
=Sum(Abs([strGuidance]="CT"))
.... etc
 
S

stevesjohn2486

Thank You very much that works great.
steve j

[email protected](donotspam). said:
Yes. the vi,ct,and us are the only entries possible. I would like have a
total count of the number of each ct's, vi's and us's. at the end of my
monthly report.


Steve Schapel said:
Steve,

It is not clear what you want. Do you mean that the VI, CT, and US
possibilities are the data in the strGuidance field? So, this is text,
not numerical, so you can't get a sum of this field. Do you mean the
sum of another field for each of the 3 groups according to the value of
strGuidance? Maybe an example would help to convey your meaning.

--
Steve Schapel, Microsoft Access MVP


I have field named [strGuidance]: there are 3 possiblities VI ,CT, and US. I
would like a sum of each at the end of report? is this possible? I can get
the total count but would like to break it down. How would I do this?
Thanks
steve j
Click to expand...
Click to expand...
Click to expand...
 
Top