show Count of "empty" records

Hi
I'm compiling a simple database to analyse responses to a
questionnaire, most of which are multiple choice responses. I need to
be able to have queries which show not only how many people chose each
response but also to include any response where no-one selected that
option - in other words, an empty response. Currently, all I can get
up is the number for each response that has been chosen at least once.

Seems to me that a lot of people have asked very similar questions but
although I've been searching for over an hour I can't find any reply
that tell me how to do it.

Can somebody tell me the simplest way please?

(Is NULL doesn't work as there is no data at all in unselected
responses)

"GillWeb" wrote in message
ups.com...
Hi
I'm compiling a simple database to analyse responses to a
questionnaire, most of which are multiple choice responses. I need to
be able to have queries which show not only how many people chose each
response but also to include any response where no-one selected that
option - in other words, an empty response. Currently, all I can get
up is the number for each response that has been chosen at least once.

Seems to me that a lot of people have asked very similar questions but
although I've been searching for over an hour I can't find any reply
that tell me how to do it.

Can somebody tell me the simplest way please?

(Is NULL doesn't work as there is no data at all in unselected
responses)


You need to post some details about how your tables are set up. Are you
saying that you have a table full of questions and some answer fields are
empty? IsNull ought to work in that scenario but you might have zero-length
strings in which case use the Nz function.

Keith.
www.keithwilby.com

Post your table structure and your query SQL.
--
KARL DEWEY
Build a little - Test a little


"GillWeb" wrote:

Hi
I'm compiling a simple database to analyse responses to a
questionnaire, most of which are multiple choice responses. I need to
be able to have queries which show not only how many people chose each
response but also to include any response where no-one selected that
option - in other words, an empty response. Currently, all I can get
up is the number for each response that has been chosen at least once.

Seems to me that a lot of people have asked very similar questions but
although I've been searching for over an hour I can't find any reply
that tell me how to do it.

Can somebody tell me the simplest way please?

(Is NULL doesn't work as there is no data at all in unselected
responses)

On Jun 12, 3:16 pm, "Keith Wilby" wrote:
"GillWeb" wrote in message

ups.com...





Hi
I'm compiling a simple database to analyse responses to a
questionnaire, most of which are multiple choice responses. I need to
be able to have queries which show not only how many people chose each
response but also to include any response where no-one selected that
option - in other words, an empty response. Currently, all I can get
up is the number for each response that has been chosen at least once.

Seems to me that a lot of people have asked very similar questions but
although I've been searching for over an hour I can't find any reply
that tell me how to do it.

Can somebody tell me the simplest way please?

(Is NULL doesn't work as there is no data at all in unselected
responses)

You need to post some details about how your tables are set up. Are you
saying that you have a table full of questions and some answer fields are
empty? IsNull ought to work in that scenario but you might have zero-length
strings in which case use the Nz function.

Keith.www.keithwilby.com- Hide quoted text -

- Show quoted text -

My table with questions uses "pick from list" for values I've typed in
for the responses, which might be along the lines of "Strongly Agree",
"Agree", "Disagree" etc. So if a person "Agrees" then the other
responses are not chosen in that record, of course.

When I try to run the query (one to analyse each question) it only
show me how many have actually chosen a particular "pick form list"
but not the ones where no-one has chosen that response to the
question. But I need to show them all, eventually, in a chart format.
At present this would only show the positives and leave out any
response which has never been chosen.

Hope I've made myself clear....

You still did not "post some details about how your tables are set up."
--
KARL DEWEY
Build a little - Test a little


"GillWeb" wrote:

On Jun 12, 3:16 pm, "Keith Wilby" wrote:
"GillWeb" wrote in message

ups.com...





Hi
I'm compiling a simple database to analyse responses to a
questionnaire, most of which are multiple choice responses. I need to
be able to have queries which show not only how many people chose each
response but also to include any response where no-one selected that
option - in other words, an empty response. Currently, all I can get
up is the number for each response that has been chosen at least once.

Seems to me that a lot of people have asked very similar questions but
although I've been searching for over an hour I can't find any reply
that tell me how to do it.

Can somebody tell me the simplest way please?

(Is NULL doesn't work as there is no data at all in unselected
responses)

You need to post some details about how your tables are set up. Are you
saying that you have a table full of questions and some answer fields are
empty? IsNull ought to work in that scenario but you might have zero-length
strings in which case use the Nz function.

Keith.www.keithwilby.com- Hide quoted text -

- Show quoted text -

My table with questions uses "pick from list" for values I've typed in
for the responses, which might be along the lines of "Strongly Agree",
"Agree", "Disagree" etc. So if a person "Agrees" then the other
responses are not chosen in that record, of course.

When I try to run the query (one to analyse each question) it only
show me how many have actually chosen a particular "pick form list"
but not the ones where no-one has chosen that response to the
question. But I need to show them all, eventually, in a chart format.
At present this would only show the positives and leave out any
response which has never been chosen.

Hope I've made myself clear....

"KARL DEWEY" wrote in message
...
You still did not "post some details about how your tables are set up."

I *think* I understand but I *don't* think it's easily possible unless I'm
missing the point.

I think the OP wants to count how many questions there are where no one has
answered (for example) "disagree". That's easy enough to hard code but
counting against a moving target, that it to say, flagging a question that
has no responses of a certain category from a range of categories is going
to be tricky and I don't have an immediate solution.

This is usually where an MVP steps in and posts a really simple solution and
I slap my forehead ;-)

Keith.

Well, first there needs to be some way to identify which answers are
available for which question. That is normally done in a table that shows
the questionId and the available responses. Since the OP does not seem to
have that structure I see no easy answer.

If the OP does have that structure, she/he needs to post the information on
the table structure so someone can pose a possible solution.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Keith Wilby" wrote in message
...
"KARL DEWEY" wrote in message
...
You still did not "post some details about how your tables are set up."

I *think* I understand but I *don't* think it's easily possible unless I'm
missing the point.

I think the OP wants to count how many questions there are where no one
has answered (for example) "disagree". That's easy enough to hard code
but counting against a moving target, that it to say, flagging a question
that has no responses of a certain category from a range of categories is
going to be tricky and I don't have an immediate solution.

This is usually where an MVP steps in and posts a really simple solution
and I slap my forehead ;-)

Keith.

"GillWeb" wrote in message
ups.com...
Hi
I'm compiling a simple database to analyse responses to a
questionnaire, most of which are multiple choice responses. I need
to
be able to have queries which show not only how many people chose
each
response but also to include any response where no-one selected that
option - in other words, an empty response. Currently, all I can get
up is the number for each response that has been chosen at least
once.

Seems to me that a lot of people have asked very similar questions
but
although I've been searching for over an hour I can't find any reply
that tell me how to do it.

Can somebody tell me the simplest way please?

(Is NULL doesn't work as there is no data at all in unselected
responses)


GillWeb,

Aircode to follow:

SELECT COUNT(Nz(YT1.YourColumn, 1))
FROM YourTable AS YT1
WHERE YT1.YourColumn IS NULL


Sincerely,

Chris O.

On Jun 12, 3:46 pm, KARL DEWEY
wrote:
You still did not "post some details about how your tables are set up."
--
KARL DEWEY
Build a little - Test a little



"GillWeb" wrote:
On Jun 12, 3:16 pm, "Keith Wilby" wrote:
"GillWeb" wrote in message

oups.com...

Hi
I'm compiling a simple database to analyse responses to a
questionnaire, most of which are multiple choice responses. I need to
be able to have queries which show not only how many people chose each
response but also to include any response where no-one selected that
option - in other words, an empty response. Currently, all I can get
up is the number for each response that has been chosen at least once.

Seems to me that a lot of people have asked very similar questions but
although I've been searching for over an hour I can't find any reply
that tell me how to do it.

Can somebody tell me the simplest way please?

(Is NULL doesn't work as there is no data at all in unselected
responses)

You need to post some details about how your tables are set up. Are you
saying that you have a table full of questions and some answer fields are
empty? IsNull ought to work in that scenario but you might have zero-length
strings in which case use the Nz function.

Keith.www.keithwilby.com-Hide quoted text -

- Show quoted text -

My table with questions uses "pick from list" for values I've typed in
for the responses, which might be along the lines of "Strongly Agree",
"Agree", "Disagree" etc. So if a person "Agrees" then the other
responses are not chosen in that record, of course.

When I try to run the query (one to analyse each question) it only
show me how many have actually chosen a particular "pick form list"
but not the ones where no-one has chosen that response to the
question. But I need to show them all, eventually, in a chart format.
At present this would only show the positives and leave out any
response which has never been chosen.

Hope I've made myself clear....- Hide quoted text -

- Show quoted text -


I'll try again (you can tell I'm not a genius here!)

I have a single table where each field consists of the "pick-from-
list" options for one of the questions.
Each record consists of the choices made by any single responder to
all the questions.

From this table I have created a query for each field to group by the
response and count how many of each response there is.

So if 20 people chose to "agree", 15 to "disagree", no-one chose to
"strongly agree" and no-one chose to "strongly disagree", then when I
run the query all I see are the responses with 20 & 15 no mention of
the other two options. What I want is for it to actually show me that
there were 0 people who chose either of those options.

Hope that clarifies what I'm looking for - I just need a way to get it
show me ALL the values not just the positive numbers - to actually
return "0" for any option where no-one has made that choice and is
"empty"

On Jun 13, 2:08 am, "Chris2"
wrote:
"GillWeb" wrote in message

ups.com...





Hi
I'm compiling a simple database to analyse responses to a
questionnaire, most of which are multiple choice responses. I need
to
be able to have queries which show not only how many people chose
each
response but also to include any response where no-one selected that
option - in other words, an empty response. Currently, all I can get
up is the number for each response that has been chosen at least
once.

Seems to me that a lot of people have asked very similar questions
but
although I've been searching for over an hour I can't find any reply
that tell me how to do it.

Can somebody tell me the simplest way please?

(Is NULL doesn't work as there is no data at all in unselected
responses)

GillWeb,

Aircode to follow:

SELECT COUNT(Nz(YT1.YourColumn, 1))
FROM YourTable AS YT1
WHERE YT1.YourColumn IS NULL

Sincerely,

Chris O.- Hide quoted text -

- Show quoted text -

Thanks Chris - that's wonderful - but right over my (female) head. I
don't know the term "Aircode" - I'm just a simple soul...
The help I need has to be at the "idiot's guide.." level I'm
afraid.....