Count only text fields containing data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pm - 21 May 2005 03:15 GMT

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.

Signature

Reply to this Message

pm - 21 May 2005 03:35 GMT

> My query contains a number "Yes/No" multiple choice answers to survey
> questions. On a few questions, respondents can select "Other" and fill in
[quoted text clipped - 16 lines]
>
> 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?

Reply to this Message

John Vinson - 22 May 2005 02:33 GMT

>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]

Reply to this Message

John Vinson - 22 May 2005 02:33 GMT

>My query contains a number "Yes/No" multiple choice answers to survey
>questions. On a few questions, respondents can select "Other" and fill in
[quoted text clipped - 5 lines]
>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]

Reply to this Message

pm - 22 May 2005 05:32 GMT

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
[quoted text clipped - 25 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 22 May 2005 22:50 GMT

>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]

Reply to this Message