Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Unanswered: Previous post was wrong - Please read this one

    I'm a real newbie. I have created a database with a form that allows trainers to input the evaluation responses from participants. There are 20 questions and each question has a combo box with agree, disagree, neutral, disagree, strongly disagree, n/a.

    I need to create a report that shows the question and gives a count of agree, disagree, etc, for each question, but don't know how to create something that totals the answers. Can someone help me please.

    Thank you in advance.
    Kathy

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how is your data stored

    carefull report design may be able to give you what you require (you can use the count() function to count the number of respnoses for each question). You may need to define the reports sort as "question" rather than "questionairre" order - set a group footer and place soem controls whose data source is count[<myquestionresponse])

    effectviely you have 6 responses stylistically you could do this using a series of IIF in a query, but please be warned if you go down that route it will be a pig to maintain and proove is correct.

    Personally for performance reasons I'd avoid using the sum in the question footer, butinstead use some code int he reports on format event. there is no reason why you couldn't use a series of dlookups (akthough I'd advise against using that again for pereformance), but you could do the same effect by issuing a series of sql statements. Heck you could even use fiendishly complex single SQL statement using group by.

    But in the spirit of KISS stick to a simple reprot and use count() in the section / group footer
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2004
    Posts
    6

    Counting responses into a report

    Thank you for your help healdem. I tried using count in the control source but it just gives a count of the whole field, ie, if there are 10 responses to the question, and 5 are agree, 2 are disagere, 1 is neutral, 2 disagree, and the question/field is called Good Venue, then I just get a count of 10 in the report.

    I would like my report to look like this:

    Good Venue Agree 5 Disagree 2 Neutral 1 Disagree 2
    Good Trainer Agree ... Disagree ... etc.

    Could you give me some more help with this? As I said, I'm a newbie so sql etc is a bit beyond me.

    Thanks again

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    'how is your data stored?' quoth i.
    the route to a solution is highly dependent on your answer to this question.

    if you have separate fields for good/bad/indifferent, take a look at A's query wizard --> crosstab (count): it will do the job for you with a few strokes of the mouse.
    when happy - look at the SQL view of the query the wizard built for you to gain inspiration on doing this stuff without the wizard next time.

    if not separate fields: how is your data stored?

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    lets assume the data is stored as a sinle response to each question.
    say
    0=No response
    1=strongly Disagree
    2=disagree
    3=not bothered
    4=agree
    5=strongly agree

    ie question 1 answer 5,Q2 A3 etc

    then a solution is to run a query which identifies type response
    Q1SA: iif([question1]=5,1,0)
    Q1A: iif([question1]=4,1,0)
    Q1NB: iif([question1]=3,1,0)
    Q1D: iif([question1]=2,1,0)
    Q1SD: iif([question1]=1,1,0)
    Q1NR: iif([question1]=0,1,0)

    OR if all you care about is agree/disagree

    Q1A: iif([question1]=4 OR [question1]=5,1,0)
    Q1NB: iif([question1]=3,1,0)
    Q1D: iif([question1]=2 OR [question1]=1,1,0)
    Q1NR: iif([question1]=0,1,0)

    repeat for each question (told you IIF's were a pig to design and maintain...)

    then you can either create another query using that query as the source and do the count int ehreport directly - "just" use that query as the source for your report and use the count
    bear in mind you can use varaible in group footers but not display them
    form this you should be able to get the number of responses for each question, work out the %'ge agree / disagree / dont care / no response

    but you do need to explain more how you data is stroed
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •