Expression Count

[Guest]

I have a querry with 3 fields. The first field which is called
'Call_Comments' is pulled from a table and contains some sentences. The
second & third fields contain an expression each. These below are the
expressions:

ADDED ADVANTAGE MEMBER: Count(IIf([Call_Comments] Like "*ADDED ADVANTAGE
MEMBER*","YES","NO"))

ADDED WORKLIFE REWARDS MEMBER: Count(IIf([Call_Comments] Like "*ADDED
WORKLIFE REWARDS MEMBER*","YES","NO"))

Basically the expression returns yes if the sentence 'added advantage
member' is found in the 'Call_Comments' column, and returns 'no' if it is not
found. The same for the second expresion: returns 'yes' if finds 'added
worklife rewards member' and 'no' if not found.

I would like to find a fourth expression in which counts the total of 'yes'.
Let's say if the Call_Comments table contains both ADDED ADVANTAGE MEMBER and
ADDED WORKLIFE REWARDS MEMBER each field will return 'Yes" and then I need an
expresion that would say 2.

Any help?

 

[Jeff Boyce]

Alexandra

I'm not sure I'm following...

Are you saying that you are putting expressions in fields in a table? Why?
What are you trying to accomplish by doing that?

Your expressions sound more like extra fields you'd add to a query ... why
embed them in a table with a Comments field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dale Fye]

Alexandra,

I'm unsure why you are trying to Count by these computed fields). No matter
what the value, the Count() operator is going to count 1 for each record. I
think what you probably want to do is do a SUM(IIF(......, 1, 0)) where you
replace "YES" with 1 and "NO" with 0. To get the "4th column" which tests
for Advantage and Rewards membership, the following might work (again, I
think you probably want to Sum over 1's and 0's, but I'll put it in your
format anyway.

Add another column:

ADDED ADV AND REWARDS MEMBER: COUNT(IIF([Call_Comments] Like "*ADDED
ADVANTAGE MEMBER*" AND [Call_Comments] Like "*ADDED WORKLIFE REWARDS
MEMBER*"), "YES", "NO"))

HTH
Dale

 

[Guest]

Jeff,

I put my expressions as extra fields in the query (returning either 'yes' or
'no' ), and I just need another expression that counts all the 'yes' that
returned in the fields where I have the expressions.

I hope it makes sense

Thanks

Alexandra

I'm not sure I'm following...

Are you saying that you are putting expressions in fields in a table? Why?
What are you trying to accomplish by doing that?

Your expressions sound more like extra fields you'd add to a query ... why
embed them in a table with a Comments field?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a querry with 3 fields. The first field which is called
'Call_Comments' is pulled from a table and contains some sentences. The
second & third fields contain an expression each. These below are the
expressions:

ADDED ADVANTAGE MEMBER: Count(IIf([Call_Comments] Like "*ADDED ADVANTAGE
MEMBER*","YES","NO"))

ADDED WORKLIFE REWARDS MEMBER: Count(IIf([Call_Comments] Like "*ADDED
WORKLIFE REWARDS MEMBER*","YES","NO"))

Basically the expression returns yes if the sentence 'added advantage
member' is found in the 'Call_Comments' column, and returns 'no' if it is
not
found. The same for the second expresion: returns 'yes' if finds 'added
worklife rewards member' and 'no' if not found.

I would like to find a fourth expression in which counts the total of
'yes'.
Let's say if the Call_Comments table contains both ADDED ADVANTAGE MEMBER
and
ADDED WORKLIFE REWARDS MEMBER each field will return 'Yes" and then I need
an
expresion that would say 2.

Any help?

 

[John Spencer]

Count counts the presence of a value. The only thing Count does not count
is NULL. So you can rewrite your calculated fields as

ADDED ADVANTAGE MEMBER:
Count(IIf([Call_Comments] Like "*ADDED ADVANTAGE MEMBER*",1, Null))

ADDED WORKLIFE REWARDS MEMBER:
Count(IIf([Call_Comments] Like "*ADDED WORKLIFE REWARDS
MEMBER*","YES",Null))

You could also use the following.
ADDED ADVANTAGE MEMBER:
Abs(Sum([Call_Comments] Like "*ADDED ADVANTAGE MEMBER*"))

Explanation
[Call_Comments] Like "*ADDED ADVANTAGE MEMBER*" will return True ( which
is -1 in Access Jet ) or False (0)

Sum adds all that together

Abs removes the negative sign

Hope that helps