Countif

[Sara Mellen]

This is from an Access non-expert--no sql background and little vba.

Is there an equivalent Access expression to Excel's "countif" function?
This could be done in a query or as a calculated control on a report.

This field has one of four values appearing in it: 1, 2, 3 or 4. I need to
count how many times each value appears and report them. So for example, if
the number "4" appears in 10 records, the formula would report the count of
"10".

The second part of this is that I need to then do a percent of each value.
In other words, if there are 100 answers, including 2 4s, 2 3s, 3 2s and 3
1s, the fields would report 20%, 20%, 30%, and 30%.

Thanks!

Sara

 

[Duane Hookom]

To count the number of times that 4 appears, use a text box in a group or
report header or footer like:
=Sum( Abs( [YourField] = 4) )
Part 2:
=Sum( Abs( [YourField] = 4) ) / Count([YourField])
or
=IIf(Count([YourField])>0, Sum( Abs( [YourField] = 4) ) /
Count([YourField]), 0)

 

[Ted Allen]

Hi Sara,

In addition to the method that Duane already mentioned,
if you are only interested in the group summary, and do
not need the detail records, another option would be to
create a totals query and group by the field you are
interested in. Then, add the same field to the query
again but specify count under the field name. For the
percentage, you could divide the count field by the total
number of records in the source table. To return the
count of all records in the source table, you can use the
DCount() function or a simple subquery (Something like
(SELECT Count(VT.ID) FROM YourTable AS VT))

If you are preparing a report, and you want to see the
detail as well as the group summaries, you should use the
method that Duane mentioned.

Hope that helps.

-Ted Allen

 

[Sara Mellen]

First, thanks so much for your answer! I'm having some trouble with the
grouping--let me explain the situation a little better. I have a
questionnaire here, and many of the questions have four possible answers, to
which i've attached values. So "Very good" is 4, "Good" is 3, "Fair" is 2
and "Poor" is 1. 0 is reserved for "no answer". The report needs only
summary data--how many people answered "Very Good", etc., plus what
percentage of the people that answered the question answered "Very good".

The totals query you suggest works great--but only for one answer for one
question. I'd have to create a total of about 100 queries to do this!

When I use the calculated report fields as suggested by Duane, I get great
results--but the report lists them for each record--I can't get just one
total.

Any suggestions?

Sara

 

[Sara Mellen]

Thanks so much for your help, Duane. The fields work great! I'm having
trouble with the grouping, though--I only need summary data, no detail.
Please see my reply to Ted if you have time to give me your "two cents".

Thanks again.

Sara

To count the number of times that 4 appears, use a text box in a group or
report header or footer like:
=Sum( Abs( [YourField] = 4) )
Part 2:
=Sum( Abs( [YourField] = 4) ) / Count([YourField])
or
=IIf(Count([YourField])>0, Sum( Abs( [YourField] = 4) ) /
Count([YourField]), 0)

--
Duane Hookom
MS Access MVP
--

This is from an Access non-expert--no sql background and little vba.

Is there an equivalent Access expression to Excel's "countif" function?
This could be done in a query or as a calculated control on a report.

This field has one of four values appearing in it: 1, 2, 3 or 4. I

need

to
count how many times each value appears and report them. So for

example,

if
the number "4" appears in 10 records, the formula would report the

count

of
"10".

The second part of this is that I need to then do a percent of each value.
In other words, if there are 100 answers, including 2 4s, 2 3s, 3 2s and 3
1s, the fields would report 20%, 20%, 30%, and 30%.

Thanks!

Sara

 

[Sara Mellen]

Oops--I said that wrong. I would need a separate query for each
question--not for each answer. Do you think there's a way around this?

Sara

 

[Duane Hookom]

Glad to be of assistance. I don't see a another thread from you or Ted.

--
Duane Hookom
MS Access MVP

Thanks so much for your help, Duane. The fields work great! I'm having
trouble with the grouping, though--I only need summary data, no detail.
Please see my reply to Ted if you have time to give me your "two cents".

Thanks again.

Sara

To count the number of times that 4 appears, use a text box in a group or
report header or footer like:
=Sum( Abs( [YourField] = 4) )
Part 2:
=Sum( Abs( [YourField] = 4) ) / Count([YourField])
or
=IIf(Count([YourField])>0, Sum( Abs( [YourField] = 4) ) /
Count([YourField]), 0)

--
Duane Hookom
MS Access MVP
--

This is from an Access non-expert--no sql background and little vba.

Is there an equivalent Access expression to Excel's "countif" function?
This could be done in a query or as a calculated control on a report.

This field has one of four values appearing in it: 1, 2, 3 or 4. I

need

to
count how many times each value appears and report them. So for

example,

if
the number "4" appears in 10 records, the formula would report the

count

of
"10".

The second part of this is that I need to then do a percent of each value.
In other words, if there are 100 answers, including 2 4s, 2 3s, 3 2s

and

3

 

[Duane Hookom]

Sara,
It sounds like you have un-normalized tables. You might want to consider a
structure like "At Your Survey" found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

 

[Sara Mellen]

Well, I don't know about that...but I DID get your expressions to work in my
report! Took me awhile to get the query right and get the expressions in
the right place on the report, but it's working great from a grouping
standpoint now. (I did look at the web site--lots of goodies there to look
at when I have time!).

So now I realize I have another problem--please advise if i should start
another thread. When I calculate the percents, I want it to ignore blanks.
So, let's say there are 100 records, with 80 of them completed and 20 blank
(i.e. people didn't answer that question on the survey). I want the
percents to be calculated using the 80 completed questions rather than the
100 records.

I've tried using an Iif statement, NZ (I'm not sure what I thought that was
going to do!), putting criteria in the query (made ALL the records that had
a blank on that question inaccessible, even though they had completed
answers for other questions), and I just don't seem to be able to get the
right syntax to get this done. Any suggestions? Do you want me to post the
solutions I've tried?

Thanks much!

Sara

 

[Duane Hookom]

If YourField is Null then it doesn't get "counted" in the following
expression:
=IIf(Count([YourField])>0, Sum( Abs( [YourField] = 4) ) /Count([YourField]),
0)

 

[Sara Mellen]

That's the same expression I had been using, but your answer gave me the
confidence to revisit it--and the problem wasn't the expression but the way
I was using my fields (needed to use "generalevaluation" rather than
"countofgeneralevaluation"). You have helped SO much--I really appreciate
it.

I thought you might want to know that this is to help a children's home that
treats, houses and schools children who have been the victims of abuse. The
survey helps track and improve services to these kids. Give yourself a big
pat on the back!

Sara

If YourField is Null then it doesn't get "counted" in the following
expression:
=IIf(Count([YourField])>0, Sum( Abs( [YourField] = 4) ) /Count([YourField]),
0)

--
Duane Hookom
MS Access MVP

Well, I don't know about that...but I DID get your expressions to work

in

my
report! Took me awhile to get the query right and get the expressions in
the right place on the report, but it's working great from a grouping
standpoint now. (I did look at the web site--lots of goodies there to look
at when I have time!).

So now I realize I have another problem--please advise if i should start
another thread. When I calculate the percents, I want it to ignore blanks.
So, let's say there are 100 records, with 80 of them completed and 20 blank
(i.e. people didn't answer that question on the survey). I want the
percents to be calculated using the 80 completed questions rather than the
100 records.

I've tried using an Iif statement, NZ (I'm not sure what I thought that was
going to do!), putting criteria in the query (made ALL the records that had
a blank on that question inaccessible, even though they had completed
answers for other questions), and I just don't seem to be able to get the
right syntax to get this done. Any suggestions? Do you want me to post the
solutions I've tried?

Thanks much!

Sara

consider

a

 

[Duane Hookom]

Sara,
Thanks for the reply. I was just in the Mall of America yesterday morning
and saw a t-shirt in the Lake Wobegon store with the quote:
"Nothing you do for children is ever wasted."
by Garrison Keillor

A quick search in google found this page
http://quotations.about.com/cs/inspirationquotes/a/Children1.htm.
Garrison Keillor: Quotes: Children
Nothing you do for children is ever wasted. They seem not to notice us,
hovering, averting our eyes, and they seldom offer thanks, but what we do
for them is never wasted.


--
Duane Hookom
MS Access MVP
--

That's the same expression I had been using, but your answer gave me the
confidence to revisit it--and the problem wasn't the expression but the way
I was using my fields (needed to use "generalevaluation" rather than
"countofgeneralevaluation"). You have helped SO much--I really appreciate
it.

I thought you might want to know that this is to help a children's home that
treats, houses and schools children who have been the victims of abuse. The
survey helps track and improve services to these kids. Give yourself a big
pat on the back!

Sara

If YourField is Null then it doesn't get "counted" in the following
expression:
=IIf(Count([YourField])>0, Sum( Abs( [YourField] = 4) ) /Count([YourField]),
0)

--
Duane Hookom
MS Access MVP

Well, I don't know about that...but I DID get your expressions to work

in

my
report! Took me awhile to get the query right and get the expressions in
the right place on the report, but it's working great from a grouping
standpoint now. (I did look at the web site--lots of goodies there to look
at when I have time!).

So now I realize I have another problem--please advise if i should start
another thread. When I calculate the percents, I want it to ignore blanks.
So, let's say there are 100 records, with 80 of them completed and 20 blank
(i.e. people didn't answer that question on the survey). I want the
percents to be calculated using the 80 completed questions rather than the
100 records.

I've tried using an Iif statement, NZ (I'm not sure what I thought

that
was

going to do!), putting criteria in the query (made ALL the records

that
had

a blank on that question inaccessible, even though they had completed
answers for other questions), and I just don't seem to be able to get the
right syntax to get this done. Any suggestions? Do you want me to

post
the

solutions I've tried?

Thanks much!

Sara


Sara,
It sounds like you have un-normalized tables. You might want to

consider

a
structure like "At Your Survey" found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP


Oops--I said that wrong. I would need a separate query for each
question--not for each answer. Do you think there's a way around this?

Sara


Hi Sara,

In addition to the method that Duane already mentioned,
if you are only interested in the group summary, and do
not need the detail records, another option would be to
create a totals query and group by the field you are
interested in. Then, add the same field to the query
again but specify count under the field name. For the
percentage, you could divide the count field by the total
number of records in the source table. To return the
count of all records in the source table, you can use the
DCount() function or a simple subquery (Something like
(SELECT Count(VT.ID) FROM YourTable AS VT))

If you are preparing a report, and you want to see the
detail as well as the group summaries, you should use the
method that Duane mentioned.

Hope that helps.

-Ted Allen
-----Original Message-----
This is from an Access non-expert--no sql background and
little vba.

Is there an equivalent Access expression to
Excel's "countif" function?
This could be done in a query or as a calculated control
on a report.

This field has one of four values appearing in it: 1,
2, 3 or 4. I need to
count how many times each value appears and report
them. So for example, if
the number "4" appears in 10 records, the formula would
report the count of
"10".

The second part of this is that I need to then do a
percent of each value.
In other words, if there are 100 answers, including 2
4s, 2 3s, 3 2s and 3
1s, the fields would report 20%, 20%, 30%, and 30%.

Thanks!

Sara


.

 

[Sara Mellen]

Boy, I hope you believe that, because I've got one more question. I'm
really struggling with this because it's not something I do all the time...

Everything is working great but now I can't figure out a way to take the "no
answers" out of the yes/no fields. i tried creating a 3-record lookup since
it seemed as though I needed a way to choose "no answer": 1=yes, 2=no, 3=no
answer. i can't seem to get the if statement to work:

=iif(count([myfield])<>3,Sum(Abs([myfield]=1))/Count([myfield]),0)

I also thought maybe I could use a regular yes/no field, with another yes/no
field set up just to indicate "no answer". then I assume the formula would
be something like:

=sum(abs([myfield]=1))/sum(count([myfield])-count([noanswerfield]))

I'm sure this is just brain freeze but perhaps you can thaw it!

Sara

P.S. I'll have to keep the "they seldom offer thanks" part in mind with my
unappreciative 15-year-old!

Sara,
Thanks for the reply. I was just in the Mall of America yesterday morning
and saw a t-shirt in the Lake Wobegon store with the quote:
"Nothing you do for children is ever wasted."
by Garrison Keillor

A quick search in google found this page
http://quotations.about.com/cs/inspirationquotes/a/Children1.htm.
Garrison Keillor: Quotes: Children
Nothing you do for children is ever wasted. They seem not to notice us,
hovering, averting our eyes, and they seldom offer thanks, but what we do
for them is never wasted.


--
Duane Hookom
MS Access MVP
--

That's the same expression I had been using, but your answer gave me the
confidence to revisit it--and the problem wasn't the expression but the way
I was using my fields (needed to use "generalevaluation" rather than
"countofgeneralevaluation"). You have helped SO much--I really appreciate
it.

I thought you might want to know that this is to help a children's home that
treats, houses and schools children who have been the victims of abuse. The
survey helps track and improve services to these kids. Give yourself a big
pat on the back!

Sara

If YourField is Null then it doesn't get "counted" in the following
expression:
=IIf(Count([YourField])>0, Sum( Abs( [YourField] = 4) ) /Count([YourField]),
0)

--
Duane Hookom
MS Access MVP


Well, I don't know about that...but I DID get your expressions to

work
in

my
report! Took me awhile to get the query right and get the

expressions
in

the right place on the report, but it's working great from a grouping
standpoint now. (I did look at the web site--lots of goodies there to
look
at when I have time!).

So now I realize I have another problem--please advise if i should start
another thread. When I calculate the percents, I want it to ignore
blanks.
So, let's say there are 100 records, with 80 of them completed and 20
blank
(i.e. people didn't answer that question on the survey). I want the
percents to be calculated using the 80 completed questions rather

than
the

100 records.

I've tried using an Iif statement, NZ (I'm not sure what I thought that
was
going to do!), putting criteria in the query (made ALL the records that
had
a blank on that question inaccessible, even though they had completed
answers for other questions), and I just don't seem to be able to

get
the

right syntax to get this done. Any suggestions? Do you want me to post
the
solutions I've tried?

Thanks much!

Sara


Sara,
It sounds like you have un-normalized tables. You might want to consider
a
structure like "At Your Survey" found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP


Oops--I said that wrong. I would need a separate query for each
question--not for each answer. Do you think there's a way around
this?

Sara


Hi Sara,

In addition to the method that Duane already mentioned,
if you are only interested in the group summary, and do
not need the detail records, another option would be to
create a totals query and group by the field you are
interested in. Then, add the same field to the query
again but specify count under the field name. For the
percentage, you could divide the count field by the total
number of records in the source table. To return the
count of all records in the source table, you can use the
DCount() function or a simple subquery (Something like
(SELECT Count(VT.ID) FROM YourTable AS VT))

If you are preparing a report, and you want to see the
detail as well as the group summaries, you should use the
method that Duane mentioned.

Hope that helps.

-Ted Allen
-----Original Message-----
This is from an Access non-expert--no sql background and
little vba.

Is there an equivalent Access expression to
Excel's "countif" function?
This could be done in a query or as a calculated control
on a report.

This field has one of four values appearing in it: 1,
2, 3 or 4. I need to
count how many times each value appears and report
them. So for example, if
the number "4" appears in 10 records, the formula would
report the count of
"10".

The second part of this is that I need to then do a
percent of each value.
In other words, if there are 100 answers, including 2
4s, 2 3s, 3 2s and 3
1s, the fields would report 20%, 20%, 30%, and 30%.

Thanks!

Sara


.

 

[Duane Hookom]

I don't know what you mean by "take the no answers our of the yes/no
fields". The first part of the expression just makes sure you don't divide
by zero.
Try:
=iif(count([myfield])<>0,Sum(Abs([myfield]=1))/Count([myfield]),0)
If you provide about 10 records of sample data with the expected result, I
might have a better understanding of what you need.

--
Duane Hookom
MS Access MVP
--

Boy, I hope you believe that, because I've got one more question. I'm
really struggling with this because it's not something I do all the time...

Everything is working great but now I can't figure out a way to take the "no
answers" out of the yes/no fields. i tried creating a 3-record lookup since
it seemed as though I needed a way to choose "no answer": 1=yes, 2=no, 3=no
answer. i can't seem to get the if statement to work:

=iif(count([myfield])<>3,Sum(Abs([myfield]=1))/Count([myfield]),0)

I also thought maybe I could use a regular yes/no field, with another yes/no
field set up just to indicate "no answer". then I assume the formula would
be something like:

=sum(abs([myfield]=1))/sum(count([myfield])-count([noanswerfield]))

I'm sure this is just brain freeze but perhaps you can thaw it!

Sara

P.S. I'll have to keep the "they seldom offer thanks" part in mind with my
unappreciative 15-year-old!

Sara,
Thanks for the reply. I was just in the Mall of America yesterday morning
and saw a t-shirt in the Lake Wobegon store with the quote:
"Nothing you do for children is ever wasted."
by Garrison Keillor

A quick search in google found this page
http://quotations.about.com/cs/inspirationquotes/a/Children1.htm.
Garrison Keillor: Quotes: Children
Nothing you do for children is ever wasted. They seem not to notice us,
hovering, averting our eyes, and they seldom offer thanks, but what we do
for them is never wasted.


--
Duane Hookom
MS Access MVP
--

That's the same expression I had been using, but your answer gave me the
confidence to revisit it--and the problem wasn't the expression but

the
way

I was using my fields (needed to use "generalevaluation" rather than
"countofgeneralevaluation"). You have helped SO much--I really appreciate
it.

I thought you might want to know that this is to help a children's

home
that

treats, houses and schools children who have been the victims of

abuse.
The

survey helps track and improve services to these kids. Give yourself

a
big

pat on the back!

Sara


If YourField is Null then it doesn't get "counted" in the following
expression:
=IIf(Count([YourField])>0, Sum( Abs( [YourField] = 4) )
/Count([YourField]),
0)

--
Duane Hookom
MS Access MVP


Well, I don't know about that...but I DID get your expressions to work
in
my
report! Took me awhile to get the query right and get the expressions
in
the right place on the report, but it's working great from a grouping
standpoint now. (I did look at the web site--lots of goodies

there

to to
post

 

[Sara Mellen]

I didn't explain well...let me try again. In a yes/no table field, there
are only two possible values--for yes and no. But on the written survey,
people COULD leave the question blank, and in a yes/no field there's no way
to show that. So I figured I probably couldn't use a yes/no field--i would
need to use a lookup table instead with THREE values: 1=yes, 2=no, and
3="no answer".

What I'm having trouble doing is figuring out how to EXCLUDE the "3" values
from the count of answers. Let's say there are 100 questionnaires returned.
80 of them have an answer to this question--20 leave it blank. I want to
figure the percent of yesses and noes on the basis of the 80 returned and
leave out the 20 that didn't answer the question.

Survey data for this question would be:

SurveyID Question5
1 1
2 3
3 2
4 1
5 2
6 3
7 2
8 1
9 3
10 2

So there are 3 ones, 4 2s and 3 3s. I want the percents figured on the
basis of 7 records, not 10.

Does that help?

Sara

I don't know what you mean by "take the no answers our of the yes/no
fields". The first part of the expression just makes sure you don't divide
by zero.
Try:
=iif(count([myfield])<>0,Sum(Abs([myfield]=1))/Count([myfield]),0)
If you provide about 10 records of sample data with the expected result, I
might have a better understanding of what you need.

--
Duane Hookom
MS Access MVP
--

Boy, I hope you believe that, because I've got one more question. I'm
really struggling with this because it's not something I do all the time...

Everything is working great but now I can't figure out a way to take the "no
answers" out of the yes/no fields. i tried creating a 3-record lookup since
it seemed as though I needed a way to choose "no answer": 1=yes, 2=no, 3=no
answer. i can't seem to get the if statement to work:

=iif(count([myfield])<>3,Sum(Abs([myfield]=1))/Count([myfield]),0)

I also thought maybe I could use a regular yes/no field, with another yes/no
field set up just to indicate "no answer". then I assume the formula would
be something like:

=sum(abs([myfield]=1))/sum(count([myfield])-count([noanswerfield]))

I'm sure this is just brain freeze but perhaps you can thaw it!

Sara

P.S. I'll have to keep the "they seldom offer thanks" part in mind with my
unappreciative 15-year-old!

Sara,
Thanks for the reply. I was just in the Mall of America yesterday morning
and saw a t-shirt in the Lake Wobegon store with the quote:
"Nothing you do for children is ever wasted."
by Garrison Keillor

A quick search in google found this page
http://quotations.about.com/cs/inspirationquotes/a/Children1.htm.
Garrison Keillor: Quotes: Children
Nothing you do for children is ever wasted. They seem not to notice us,
hovering, averting our eyes, and they seldom offer thanks, but what we do
for them is never wasted.


--
Duane Hookom
MS Access MVP
--


That's the same expression I had been using, but your answer gave me the
confidence to revisit it--and the problem wasn't the expression but the
way
I was using my fields (needed to use "generalevaluation" rather than
"countofgeneralevaluation"). You have helped SO much--I really appreciate
it.

I thought you might want to know that this is to help a children's home
that
treats, houses and schools children who have been the victims of abuse.
The
survey helps track and improve services to these kids. Give

yourself

a

big
pat on the back!

Sara


If YourField is Null then it doesn't get "counted" in the following
expression:
=IIf(Count([YourField])>0, Sum( Abs( [YourField] = 4) )
/Count([YourField]),
0)

--
Duane Hookom
MS Access MVP


Well, I don't know about that...but I DID get your expressions

to
work

in
my
report! Took me awhile to get the query right and get the expressions
in
the right place on the report, but it's working great from a grouping
standpoint now. (I did look at the web site--lots of goodies

there

to
look
at when I have time!).

So now I realize I have another problem--please advise if i should
start
another thread. When I calculate the percents, I want it to ignore
blanks.
So, let's say there are 100 records, with 80 of them completed

and
20

blank
(i.e. people didn't answer that question on the survey). I want the
percents to be calculated using the 80 completed questions

rather
than

the
100 records.

I've tried using an Iif statement, NZ (I'm not sure what I thought
that
was
going to do!), putting criteria in the query (made ALL the records
that
had
a blank on that question inaccessible, even though they had completed
answers for other questions), and I just don't seem to be able

to
get

the
right syntax to get this done. Any suggestions? Do you want me to
post
the
solutions I've tried?

Thanks much!

Sara


Sara,
It sounds like you have un-normalized tables. You might want to
consider
a
structure like "At Your Survey" found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP


Oops--I said that wrong. I would need a separate query for each
question--not for each answer. Do you think there's a way around
this?

Sara


Hi Sara,

In addition to the method that Duane already mentioned,
if you are only interested in the group summary, and do
not need the detail records, another option would be to
create a totals query and group by the field you are
interested in. Then, add the same field to the query
again but specify count under the field name. For the
percentage, you could divide the count field by the total
number of records in the source table. To return the
count of all records in the source table, you can use the
DCount() function or a simple subquery (Something like
(SELECT Count(VT.ID) FROM YourTable AS VT))

If you are preparing a report, and you want to see the
detail as well as the group summaries, you should use the
method that Duane mentioned.

Hope that helps.

-Ted Allen
-----Original Message-----
This is from an Access non-expert--no sql background and
little vba.

Is there an equivalent Access expression to
Excel's "countif" function?
This could be done in a query or as a calculated control
on a report.

This field has one of four values appearing in it: 1,
2, 3 or 4. I need to
count how many times each value appears and report
them. So for example, if
the number "4" appears in 10 records, the formula would
report the count of
"10".

The second part of this is that I need to then do a
percent of each value.
In other words, if there are 100 answers, including 2
4s, 2 3s, 3 2s and 3
1s, the fields would report 20%, 20%, 30%, and 30%.

Thanks!

Sara


.

 

[Duane Hookom]

All the information required has been given to you. All you have to do is
apply it.
=Sum(Abs(Question5=1))/Sum(Abs(Question5<>3))
If no one answers Question5 then you will get a divide by zero error. To fix
this, use:
=IIf( Sum(Abs(Question5<>3)) = 0,0,
Sum(Abs(Question5=1))/Sum(Abs(Question5<>3)))

--
Duane Hookom
MS Access MVP

I didn't explain well...let me try again. In a yes/no table field, there
are only two possible values--for yes and no. But on the written survey,
people COULD leave the question blank, and in a yes/no field there's no way
to show that. So I figured I probably couldn't use a yes/no field--i would
need to use a lookup table instead with THREE values: 1=yes, 2=no, and
3="no answer".

What I'm having trouble doing is figuring out how to EXCLUDE the "3" values
from the count of answers. Let's say there are 100 questionnaires returned.
80 of them have an answer to this question--20 leave it blank. I want to
figure the percent of yesses and noes on the basis of the 80 returned and
leave out the 20 that didn't answer the question.

Survey data for this question would be:

SurveyID Question5
1 1
2 3
3 2
4 1
5 2
6 3
7 2
8 1
9 3
10 2

So there are 3 ones, 4 2s and 3 3s. I want the percents figured on the
basis of 7 records, not 10.

Does that help?

Sara

I don't know what you mean by "take the no answers our of the yes/no
fields". The first part of the expression just makes sure you don't divide
by zero.
Try:
=iif(count([myfield])<>0,Sum(Abs([myfield]=1))/Count([myfield]),0)
If you provide about 10 records of sample data with the expected result, I
might have a better understanding of what you need.

--
Duane Hookom
MS Access MVP
--

Boy, I hope you believe that, because I've got one more question. I'm
really struggling with this because it's not something I do all the time...

Everything is working great but now I can't figure out a way to take

the
"no

answers" out of the yes/no fields. i tried creating a 3-record lookup since
it seemed as though I needed a way to choose "no answer": 1=yes,

2=no,
3=no

answer. i can't seem to get the if statement to work:

=iif(count([myfield])<>3,Sum(Abs([myfield]=1))/Count([myfield]),0)

I also thought maybe I could use a regular yes/no field, with another yes/no
field set up just to indicate "no answer". then I assume the formula would
be something like:

=sum(abs([myfield]=1))/sum(count([myfield])-count([noanswerfield]))

I'm sure this is just brain freeze but perhaps you can thaw it!

Sara

P.S. I'll have to keep the "they seldom offer thanks" part in mind

with
my

unappreciative 15-year-old!


Sara,
Thanks for the reply. I was just in the Mall of America yesterday morning
and saw a t-shirt in the Lake Wobegon store with the quote:
"Nothing you do for children is ever wasted."
by Garrison Keillor

A quick search in google found this page
http://quotations.about.com/cs/inspirationquotes/a/Children1.htm.
Garrison Keillor: Quotes: Children
Nothing you do for children is ever wasted. They seem not to notice us,
hovering, averting our eyes, and they seldom offer thanks, but what

we
do

for them is never wasted.

me
the

confidence to revisit it--and the problem wasn't the expression

but
the

way
I was using my fields (needed to use "generalevaluation" rather than
"countofgeneralevaluation"). You have helped SO much--I really
appreciate
it.

I thought you might want to know that this is to help a children's home
that
treats, houses and schools children who have been the victims of abuse.
The
survey helps track and improve services to these kids. Give

yourself

a
big
pat on the back!

Sara


If YourField is Null then it doesn't get "counted" in the following
expression:
=IIf(Count([YourField])>0, Sum( Abs( [YourField] = 4) )
/Count([YourField]),
0)

--
Duane Hookom
MS Access MVP


Well, I don't know about that...but I DID get your expressions to
work
in
my
report! Took me awhile to get the query right and get the
expressions
in
the right place on the report, but it's working great from a
grouping
standpoint now. (I did look at the web site--lots of goodies there
to
look
at when I have time!).

So now I realize I have another problem--please advise if i should
start
another thread. When I calculate the percents, I want it to ignore
blanks.
So, let's say there are 100 records, with 80 of them completed and
20
blank
(i.e. people didn't answer that question on the survey). I

want
the

percents to be calculated using the 80 completed questions rather
than
the
100 records.

I've tried using an Iif statement, NZ (I'm not sure what I thought
that
was
going to do!), putting criteria in the query (made ALL the records
that
had
a blank on that question inaccessible, even though they had
completed
answers for other questions), and I just don't seem to be able to
get
the
right syntax to get this done. Any suggestions? Do you want

me
to

post
the
solutions I've tried?

Thanks much!

Sara


Sara,
It sounds like you have un-normalized tables. You might want to
consider
a
structure like "At Your Survey" found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

--
Duane Hookom
MS Access MVP


Oops--I said that wrong. I would need a separate query

for
each

question--not for each answer. Do you think there's a way
around
this?

Sara


message
Hi Sara,

In addition to the method that Duane already mentioned,
if you are only interested in the group summary, and do
not need the detail records, another option would be to
create a totals query and group by the field you are
interested in. Then, add the same field to the query
again but specify count under the field name. For the
percentage, you could divide the count field by the total
number of records in the source table. To return the
count of all records in the source table, you can use the
DCount() function or a simple subquery (Something like
(SELECT Count(VT.ID) FROM YourTable AS VT))

If you are preparing a report, and you want to see the
detail as well as the group summaries, you should use the
method that Duane mentioned.

Hope that helps.

-Ted Allen
-----Original Message-----
This is from an Access non-expert--no sql background and
little vba.

Is there an equivalent Access expression to
Excel's "countif" function?
This could be done in a query or as a calculated control
on a report.

This field has one of four values appearing in it: 1,
2, 3 or 4. I need to
count how many times each value appears and report
them. So for example, if
the number "4" appears in 10 records, the formula would
report the count of
"10".

The second part of this is that I need to then do a
percent of each value.
In other words, if there are 100 answers, including 2
4s, 2 3s, 3 2s and 3
1s, the fields would report 20%, 20%, 30%, and 30%.

Thanks!

Sara


.

 

[Sara Mellen]

Thanks again. Sara

All the information required has been given to you. All you have to do is
apply it.
=Sum(Abs(Question5=1))/Sum(Abs(Question5<>3))
If no one answers Question5 then you will get a divide by zero error. To fix
this, use:
=IIf( Sum(Abs(Question5<>3)) = 0,0,
Sum(Abs(Question5=1))/Sum(Abs(Question5<>3)))

--
Duane Hookom
MS Access MVP

I didn't explain well...let me try again. In a yes/no table field, there
are only two possible values--for yes and no. But on the written survey,
people COULD leave the question blank, and in a yes/no field there's no way
to show that. So I figured I probably couldn't use a yes/no field--i would
need to use a lookup table instead with THREE values: 1=yes, 2=no, and
3="no answer".

What I'm having trouble doing is figuring out how to EXCLUDE the "3" values
from the count of answers. Let's say there are 100 questionnaires returned.
80 of them have an answer to this question--20 leave it blank. I want to
figure the percent of yesses and noes on the basis of the 80 returned and
leave out the 20 that didn't answer the question.

Survey data for this question would be:

SurveyID Question5
1 1
2 3
3 2
4 1
5 2
6 3
7 2
8 1
9 3
10 2

So there are 3 ones, 4 2s and 3 3s. I want the percents figured on the
basis of 7 records, not 10.

Does that help?

Sara

I don't know what you mean by "take the no answers our of the yes/no
fields". The first part of the expression just makes sure you don't divide
by zero.
Try:
=iif(count([myfield])<>0,Sum(Abs([myfield]=1))/Count([myfield]),0)
If you provide about 10 records of sample data with the expected

result,

I

might have a better understanding of what you need.

--
Duane Hookom
MS Access MVP
--

Boy, I hope you believe that, because I've got one more question. I'm
really struggling with this because it's not something I do all the
time...

Everything is working great but now I can't figure out a way to take the
"no
answers" out of the yes/no fields. i tried creating a 3-record lookup
since
it seemed as though I needed a way to choose "no answer": 1=yes, 2=no,
3=no
answer. i can't seem to get the if statement to work:

=iif(count([myfield])<>3,Sum(Abs([myfield]=1))/Count([myfield]),0)

I also thought maybe I could use a regular yes/no field, with another
yes/no
field set up just to indicate "no answer". then I assume the formula
would
be something like:

=sum(abs([myfield]=1))/sum(count([myfield])-count([noanswerfield]))

I'm sure this is just brain freeze but perhaps you can thaw it!

Sara

P.S. I'll have to keep the "they seldom offer thanks" part in mind with
my
unappreciative 15-year-old!


Sara,
Thanks for the reply. I was just in the Mall of America yesterday
morning
and saw a t-shirt in the Lake Wobegon store with the quote:
"Nothing you do for children is ever wasted."
by Garrison Keillor

A quick search in google found this page
http://quotations.about.com/cs/inspirationquotes/a/Children1.htm.
Garrison Keillor: Quotes: Children
Nothing you do for children is ever wasted. They seem not to

notice
us,

hovering, averting our eyes, and they seldom offer thanks, but

what

we

gave

me

the
confidence to revisit it--and the problem wasn't the expression but
the
way
I was using my fields (needed to use "generalevaluation" rather than
"countofgeneralevaluation"). You have helped SO much--I really
appreciate
it.

I thought you might want to know that this is to help a children's
home
that
treats, houses and schools children who have been the victims of
abuse.
The
survey helps track and improve services to these kids. Give yourself
a
big
pat on the back!

Sara


If YourField is Null then it doesn't get "counted" in the following
expression:
=IIf(Count([YourField])>0, Sum( Abs( [YourField] = 4) )
/Count([YourField]),
0)

--
Duane Hookom
MS Access MVP


Well, I don't know about that...but I DID get your

expressions
to

work
in
my
report! Took me awhile to get the query right and get the
expressions
in
the right place on the report, but it's working great from a
grouping
standpoint now. (I did look at the web site--lots of goodies
there
to
look
at when I have time!).

So now I realize I have another problem--please advise if i should
start
another thread. When I calculate the percents, I want it to
ignore
blanks.
So, let's say there are 100 records, with 80 of them

completed
and

20
blank
(i.e. people didn't answer that question on the survey). I want
the
percents to be calculated using the 80 completed questions rather
than
the
100 records.

I've tried using an Iif statement, NZ (I'm not sure what I thought
that
was
going to do!), putting criteria in the query (made ALL the records
that
had
a blank on that question inaccessible, even though they had
completed
answers for other questions), and I just don't seem to be

able
to

get
the
right syntax to get this done. Any suggestions? Do you

want

me want
to