Counting different results in a field.... HELP PLEASE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a questionnaire database (access 2000). table example below

tblquestionnair

Field names Field Typ

Quesiton 1 numbe
Question 1 Comments mem
Question 2 tex
Question 3 numbe
Question 3 Comments memo
Quesiton 4 numbe
Question 4 Comments mem
Quesiton 5 tex

tblquestion2answer

Field
Lexingto
Regional
Bot

I have set up a query for reporting purposes that averages the number fields which are answers to ratings quesitons on a scale 1 to 10. question 2 is text field which has its row sourced set to field list with a row source of "tblquestion2answers".

What I need to do is have the query count the total of each responce to question 2.. ie.... 22 Lexington, 50 Both, 50 Regional.

This seems like a pretty simple thing, but I have been having a very difficult time finding any suggestions.
 
I'm having the exact same problem! I thought it should
be simple, but I have had to do it what I think is a
roundabout way. In my case I wanted a report which
listed the number of yes and no answers for each question
in a questionnaire and had to do this by putting a Dcount
function into a text box on the report. It works, but is
tedious as you have to do two text boxes for each and
every question.
I'd be grateful to find an easier way.
-----Original Message-----
I have a questionnaire database (access 2000). table example below.

tblquestionnaire

Field names Field Type

Quesiton 1 number
Question 1 Comments memo
Question 2 text
Question 3 number
Question 3 Comments memo
Quesiton 4 number
Question 4 Comments memo
Quesiton 5 text

tblquestion2answers

Fields
Lexington
Regional
Both


I have set up a query for reporting purposes that
Click to expand...
averages the number fields which are answers to ratings
quesitons on a scale 1 to 10. question 2 is text field
which has its row sourced set to field list with a row
source of "tblquestion2answers".
What I need to do is have the query count the total of
Click to expand...
each responce to question 2.. ie.... 22 Lexington, 50
Both, 50 Regional.
This seems like a pretty simple thing, but I have been
Click to expand...
having a very difficult time finding any suggestions.
 
Sally,

That can be done I have pasted part of a post I had previously seen. You should be able to find it if you search for "counting yes & no" in the search bar above the forum. The guy who posted this below has a website with free tips, I have picked up a few good things there, and he has helpd me with some post.

Hope this helps you!!


Access stores Yes as -1, and No as 0. You can therefore sum the values to
get the number of yeses.

On a form or report:
=Abs(Sum([MyYesNoField])))

In other contexts:
=DSum("MyYesNoField], "MyTable")
 
If your survey was more normalized, you wouldn't have this type of issue.
There is a demo "At Your Survey" that has a report similar to what you are
asking for
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
You could normalize your data using a UNION query. Then a simple Totals
Query could be used to summarize your questions.

--
Duane Hookom
MS Access MVP


BDP said:
Sally,

That can be done I have pasted part of a post I had previously seen. You
Click to expand...
should be able to find it if you search for "counting yes & no" in the
search bar above the forum. The guy who posted this below has a website with
free tips, I have picked up a few good things there, and he has helpd me
with some post.
Hope this helps you!!


Access stores Yes as -1, and No as 0. You can therefore sum the values to
get the number of yeses.

On a form or report:
=Abs(Sum([MyYesNoField])))

In other contexts:
=DSum("MyYesNoField], "MyTable")
Click to expand...
 
Thank you Duane

I have downloaded the demo and will definitly take a look at this info, the page looks like it could be a very usefull tool for an Access newcomer like me

I am not sure what you mean by "normalized" but thank you for the tip

Barry
 
You may have a better understanding of normalization after reviewing your
table structure and the one I used in At Your Survey. There are lots of
other resources on the web regarding database normalization.

--
Duane Hookom
Microsoft Access MVP


BDP said:
Thank you Duane,

I have downloaded the demo and will definitly take a look at this info,
Click to expand...
the page looks like it could be a very usefull tool for an Access newcomer
like me.
 
Thank you, that's a help - this is quite a good way of
doing it, although I quite like my Dcount too!
-----Original Message-----
Sally,

That can be done I have pasted part of a post I had
Click to expand...
previously seen. You should be able to find it if you
search for "counting yes & no" in the search bar above
the forum. The guy who posted this below has a website
with free tips, I have picked up a few good things there,
and he has helpd me with some post.
Hope this helps you!!


Access stores Yes as -1, and No as 0. You can therefore sum the values to
get the number of yeses.

On a form or report:
=Abs(Sum([MyYesNoField])))

In other contexts:
=DSum("MyYesNoField], "MyTable")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

.
Click to expand...
 
Back
Top