Count only text fields containing data

[pm]

My query contains a number "Yes/No" multiple choice answers to survey
questions. On a few questions, respondents can select "Other" and fill in
something other than a yes/no.

So, two issues:

1. When I use the Sum function in the query for my Yes/No fields, the total
is registered as a negative number. I want this to be a positive number so I
can divide this number by the number of records to see a percentage.

2. I want to count only the number of "Other" fields that contain data so I
can get a number I can divide to see percentages, as above.

I did create an Excel file and change the values (Yes=1 and No=0), then
imported this into Access as a new table. However, I am stumped how to assign
a value and count the number of text entries made in the "Other" field.

Simple steps/language, please.

Thanks.

 

[pm]

My query contains a number "Yes/No" multiple choice answers to survey
questions. On a few questions, respondents can select "Other" and fill in
something other than a yes/no.

So, two issues:

1. When I use the Sum function in the query for my Yes/No fields, the total
is registered as a negative number. I want this to be a positive number so I
can divide this number by the number of records to see a percentage.

2. I want to count only the number of "Other" fields that contain data so I
can get a number I can divide to see percentages, as above.

I did create an Excel file and change the values (Yes=1 and No=0), then
imported this into Access as a new table. However, I am stumped how to assign
a value and count the number of text entries made in the "Other" field.

Simple steps/language, please.

Thanks.

One more thing...

Now that I have values in the yes/no answer fields, how do I add them up to
get a total?

 

[John Vinson]

My query contains a number "Yes/No" multiple choice answers to survey
questions. On a few questions, respondents can select "Other" and fill in
something other than a yes/no.

So, two issues:

1. When I use the Sum function in the query for my Yes/No fields, the total
is registered as a negative number. I want this to be a positive number so I
can divide this number by the number of records to see a percentage.

-Sum([yesnofield])

will turn the negative Sum to a positive; or use Abs(Sum(...)).

2. I want to count only the number of "Other" fields that contain data so I
can get a number I can divide to see percentages, as above.

Sum(IIf(IsNull([Other]), 0, 1))

The IIF will return 0 if Other is empty, 1 if it isn't; you can then
sum this value.

I did create an Excel file and change the values (Yes=1 and No=0), then
imported this into Access as a new table. However, I am stumped how to assign
a value and count the number of text entries made in the "Other" field.

MUCH more than is needed!


John W. Vinson[MVP]

 

[John Vinson]

Now that I have values in the yes/no answer fields, how do I add them up to
get a total?

To count Yeses:

Abs(Sum([yesnofield]))

To count Nos:

Sum([yesnofield] + 1)

John W. Vinson[MVP]

 

[pm]

Thank-you for your response.

Yes, I know the Excel route is superfluous, but doing simple things in
Access is difficult - at least it seems to be for me as I'm teaching myself.
Converting a value in Excel is easier but I figured I should be able to do it
in Access, so have been working this all day and most of the night yesterday
and today. If I HAD used the Excel work-around, I'd probably have all my
reports done!

I will try your solutions - thanks for the help.

My query contains a number "Yes/No" multiple choice answers to survey
questions. On a few questions, respondents can select "Other" and fill in
something other than a yes/no.

So, two issues:

1. When I use the Sum function in the query for my Yes/No fields, the total
is registered as a negative number. I want this to be a positive number so I
can divide this number by the number of records to see a percentage.

-Sum([yesnofield])

will turn the negative Sum to a positive; or use Abs(Sum(...)).

2. I want to count only the number of "Other" fields that contain data so I
can get a number I can divide to see percentages, as above.

Sum(IIf(IsNull([Other]), 0, 1))

The IIF will return 0 if Other is empty, 1 if it isn't; you can then
sum this value.

I did create an Excel file and change the values (Yes=1 and No=0), then
imported this into Access as a new table. However, I am stumped how to assign
a value and count the number of text entries made in the "Other" field.

MUCH more than is needed!


John W. Vinson[MVP]

 

[John Vinson]

Thank-you for your response.

Yes, I know the Excel route is superfluous, but doing simple things in
Access is difficult - at least it seems to be for me as I'm teaching myself.

I know the feeling! Sometimes I find that "unlearning" the way "this
is always done" is harder than learning from scratch. If you're coming
from an Excel background, and know easy ways to do things in Excel,
they may or may not apply in Access; and you can beat your head
against the wall trying to do something one way, without realizing
that it can be done more easily in a quite different manner.

Converting a value in Excel is easier but I figured I should be able to do it
in Access, so have been working this all day and most of the night yesterday
and today. If I HAD used the Excel work-around, I'd probably have all my
reports done!

Well, don't hesitate to post questions here when you start running
into problems. The volunteers are pretty quick to pick up questions,
most of the time.

John W. Vinson[MVP]