Count Question

[Jennie]

I have a table with about 30-40 columns. Each row of each column has a series
of Y's and N's. I need to count how many Y's and how many N's are in each,
and then get a overall percentage for each column. Is this possible, or is
there an easier way to do it? I am tracking the asnwers to survey questions
if that helps. Thanks!

 

[John W. Vinson]

I have a table with about 30-40 columns. Each row of each column has a series
of Y's and N's. I need to count how many Y's and how many N's are in each,
and then get a overall percentage for each column. Is this possible, or is
there an easier way to do it? I am tracking the asnwers to survey questions
if that helps. Thanks!

You've fallen victim to the "each question as a field" trap that is very
common when designing survey data. It's a good spreadsheet design, but a bad
database design! Every time you need to add or remove a question you end up
having to redesign your tables, your forms, your queries, etc.

Much better is to have a normalized design with a table of Questions (with
30-40 *rows* not columns); a table of Questionnaires, with fields for who
answered the survey, when, etc; and a table of Answers, with fields for the
QuestionNo linked to the Questions table, the QuestionnaireID linked to the
Questionnaires table, and a yes/no field (or other datatypes if you wish) for
the answer. Then a simple Totals query can count, average, sum, etc. the
answers for you.

If you're stuck with the current design, you can count the number of Yes
answers by taking advantage of the fact that Yes is stored as a number, -1,
and No as zero: to count the yesses, use an expression like

-([Q1]+[Q2]+[Q3]+[Q4]+...+[Q38])

for Yes, and

38 + ([Q1]+[Q2]+[Q3]+[Q4]+...+[Q38])

for No, assuming 38 questions.

For an example of a normalized survey database, download Duane Hookum's
excellent AtYourSurvey 2000 database:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

John W. Vinson [MVP]

 

[fredg]

I have a table with about 30-40 columns. Each row of each column has a series
of Y's and N's. I need to count how many Y's and how many N's are in each,
and then get a overall percentage for each column. Is this possible, or is
there an easier way to do it? I am tracking the asnwers to survey questions
if that helps. Thanks!

Each field is a Text datatype?
In an unbound control:
=ABS(Sum([FieldName] = "Y"))
or
=ABS(Sum([FieldName] = "N"))
or
=Sum(IIf([FieldName] = "Y",1,0))
or
=Sum(IIf([FieldName] = "N",1,0))

Off the top of my head....
To get the percentage of "Y" entries, for example:
= ABS(Sum([FieldName] = "Y"))/ Sum(IIf(Not IsNull([FieldName]),1,0))

Only records that have an entry will be included in the Divisor.

Fred

 

[Jennie]

I tried

ABS(Sum([FieldName] = "Y"))

And it works if there is only one "Y" in the field. I need to be able to
type several responses (ex. YYYNNNNNN). If I type anything other than one
"Y", the unbound control counts zero. Any suggestions?

Jennie

I have a table with about 30-40 columns. Each row of each column has a series
of Y's and N's. I need to count how many Y's and how many N's are in each,
and then get a overall percentage for each column. Is this possible, or is
there an easier way to do it? I am tracking the asnwers to survey questions
if that helps. Thanks!

Each field is a Text datatype?
In an unbound control:
=ABS(Sum([FieldName] = "Y"))
or
=ABS(Sum([FieldName] = "N"))
or
=Sum(IIf([FieldName] = "Y",1,0))
or
=Sum(IIf([FieldName] = "N",1,0))

Off the top of my head....
To get the percentage of "Y" entries, for example:
= ABS(Sum([FieldName] = "Y"))/ Sum(IIf(Not IsNull([FieldName]),1,0))

Only records that have an entry will be included in the Divisor.

Fred

 

[Douglas J. Steele]

You'll have to write a function that returns how many Ys are in a field, and
then sum that function.

Something like:

Function CountYs(InputString As Variant) As Long
Dim lngCount As Long
Dim lngLoop As Long

lngCount = 0

If IsNull(InputString) = False Then
For lngLoop = 1 To Len(InputString)
If Mid$(InputString, lngLoop, 1) = "Y" Then
lngCount = lngCount + 1
End If
Next lngLoop
End If

CountYs = lngCount

End Function

You can then use

Sum(CountYs([FieldName])

in your query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried

ABS(Sum([FieldName] = "Y"))

And it works if there is only one "Y" in the field. I need to be able to
type several responses (ex. YYYNNNNNN). If I type anything other than one
"Y", the unbound control counts zero. Any suggestions?

Jennie

I have a table with about 30-40 columns. Each row of each column has a series
of Y's and N's. I need to count how many Y's and how many N's are in
each,
and then get a overall percentage for each column. Is this possible, or
is
there an easier way to do it? I am tracking the asnwers to survey questions
if that helps. Thanks!

Each field is a Text datatype?
In an unbound control:
=ABS(Sum([FieldName] = "Y"))
or
=ABS(Sum([FieldName] = "N"))
or
=Sum(IIf([FieldName] = "Y",1,0))
or
=Sum(IIf([FieldName] = "N",1,0))

Off the top of my head....
To get the percentage of "Y" entries, for example:
= ABS(Sum([FieldName] = "Y"))/ Sum(IIf(Not IsNull([FieldName]),1,0))

Only records that have an entry will be included in the Divisor.

Fred

 

[Jennie]

I'm an Access rookie, so where would I put that function? Thank you for your
help.

You'll have to write a function that returns how many Ys are in a field, and
then sum that function.

Something like:

Function CountYs(InputString As Variant) As Long
Dim lngCount As Long
Dim lngLoop As Long

lngCount = 0

If IsNull(InputString) = False Then
For lngLoop = 1 To Len(InputString)
If Mid$(InputString, lngLoop, 1) = "Y" Then
lngCount = lngCount + 1
End If
Next lngLoop
End If

CountYs = lngCount

End Function

You can then use

Sum(CountYs([FieldName])

in your query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried

ABS(Sum([FieldName] = "Y"))

And it works if there is only one "Y" in the field. I need to be able to
type several responses (ex. YYYNNNNNN). If I type anything other than one
"Y", the unbound control counts zero. Any suggestions?

Jennie

I have a table with about 30-40 columns. Each row of each column has a
series
of Y's and N's. I need to count how many Y's and how many N's are in
each,
and then get a overall percentage for each column. Is this possible, or
is
there an easier way to do it? I am tracking the asnwers to survey
questions
if that helps. Thanks!


Each field is a Text datatype?
In an unbound control:
=ABS(Sum([FieldName] = "Y"))
or
=ABS(Sum([FieldName] = "N"))
or
=Sum(IIf([FieldName] = "Y",1,0))
or
=Sum(IIf([FieldName] = "N",1,0))

Off the top of my head....
To get the percentage of "Y" entries, for example:
= ABS(Sum([FieldName] = "Y"))/ Sum(IIf(Not IsNull([FieldName]),1,0))

Only records that have an entry will be included in the Divisor.

Fred

 

[Douglas J. Steele]

That function would go into a module (not a class module, nor a module
associated with a form). Make sure you do not name the module CountYs:
modules cannot be named the same as routines contained within them.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm an Access rookie, so where would I put that function? Thank you for
your
help.

You'll have to write a function that returns how many Ys are in a field,
and
then sum that function.

Something like:

Function CountYs(InputString As Variant) As Long
Dim lngCount As Long
Dim lngLoop As Long

lngCount = 0

If IsNull(InputString) = False Then
For lngLoop = 1 To Len(InputString)
If Mid$(InputString, lngLoop, 1) = "Y" Then
lngCount = lngCount + 1
End If
Next lngLoop
End If

CountYs = lngCount

End Function

You can then use

Sum(CountYs([FieldName])

in your query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tried

ABS(Sum([FieldName] = "Y"))

And it works if there is only one "Y" in the field. I need to be able
to
type several responses (ex. YYYNNNNNN). If I type anything other than
one
"Y", the unbound control counts zero. Any suggestions?

Jennie

:


I have a table with about 30-40 columns. Each row of each column has
a
series
of Y's and N's. I need to count how many Y's and how many N's are in
each,
and then get a overall percentage for each column. Is this possible,
or
is
there an easier way to do it? I am tracking the asnwers to survey
questions
if that helps. Thanks!


Each field is a Text datatype?
In an unbound control:
=ABS(Sum([FieldName] = "Y"))
or
=ABS(Sum([FieldName] = "N"))
or
=Sum(IIf([FieldName] = "Y",1,0))
or
=Sum(IIf([FieldName] = "N",1,0))

Off the top of my head....
To get the percentage of "Y" entries, for example:
= ABS(Sum([FieldName] = "Y"))/ Sum(IIf(Not IsNull([FieldName]),1,0))

Only records that have an entry will be included in the Divisor.

Fred