Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2004
    Posts
    31

    Unanswered: Radio choice results count

    I have done a form with radio choice results.
    (Multi-choice - select one radio button from "Strongly Agree", "Agree", "Disagree", "Strongly Disagree")
    What I need to be able to do is to count, for each Answer - how many answered "Strongly Agree", how many answered "Agree, etc...
    And put this all in a report.
    I'm a dunce when it comes to SQL and VB! So if I can fathom it out using the basics, that would be great if you could help in as simple manner as possible.
    Thanx so much!
    Merry Christmas.
    Regards,
    Jillian

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Write a query which use the count predicate
    eg
    select distinct(count([columnname])) from table
    or it could be
    select count(distinct([columnname])) from table

    not sure of the precise syntax, but I'm pretty sure I read it int he help files today.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    SELECT COUNT(SomeColumnName) FROM SomeTable GROUP BY SomeColumnName;
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Oct 2004
    Posts
    31
    Thank you, I'm just about to try it now.
    Hope you had a great Christmas!
    Regards,
    Jillian

  5. #5
    Join Date
    Oct 2004
    Posts
    31
    Sorry, but neither suggestions seem to work.
    Is it me?

    Thanx
    Regards,
    Jillian

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you mention a form, but do you have this agree/not stuff in a table ???
    no table = no query = no chance.

    what does the table look like?

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Oct 2004
    Posts
    31
    The form is set up for a survey. There are Questions (Q1, Q2, Q3, etc).
    There are 4 radio buttons for each question (Agree strongly, agree, disagree, disagree strongly). In the table the column fields are labeled Q1, Q2, Q3, etc, accordingly. When a radio button on the form is selected the column field are populated with ONE of the four selections. i.e. the table records, in field column Q1, etc will be populated with either "Agree strongly, agree, disagree, or disagree strongly.
    I want to do a report that would give me a summary total (count) of all the times Agree strongly, agree, disagree, or disagree strongly appears in EACH field column. For example if I sent out a survey to 10 people, I want to print a report that tells me that 3 people responded "Agree strongly", 6 responded "Agree", and 1 "Disagree" for question Q1, same for Q2, etc.
    So I guess it has to find distinct, as well as count for each field.
    Just want to save from having to count how many responded what to each question.
    Thank you.
    Regards,
    Jillian

  8. #8
    Join Date
    Oct 2004
    Posts
    31
    The survey works just fine. I have NO problems with that at all. When I look at the table results the fields Q1, etc ARE populated with one of the 4 choices.
    Regards,
    Jillian

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    looks like a table design question!

    if i read you correctly, you currently have something like:

    tblResponder:
    IDresp, auto PK
    strResp, name of the responder

    tblResponses:
    IDresp, auto PK
    IDperson, FK on tblResponder
    strQ1, text, what was the question
    answerQ1, ?
    strQ2, text, what was the question
    answerQ2, ?
    strQ3, text, what was the question
    answerQ3, ?

    the problem with this approach is that when you add a fourth question sometime later, you have to redefine your table and all your queries. in general, if you find you need to modify table design to add something to your database then you almost certainly have the wrong table design.

    an improved structure could be:

    tblQuestion:
    IDques, auto, PK
    strQues, text, what was the question

    tblResponder:
    IDresp, auto PK
    strResp, name of the responder

    tblAnswer:
    IDanswer, auto, PK
    IDresp, FK on tblResponder
    IDques, FK on tblQuestion
    Answer, ?

    now it is simpler to add a new question - simply add a record in tblQuestion.

    also, tblAnswer now directly gives you what you are looking for with count/group along the lines of M Owen's suggestion earlier.

    i would also be tempted to add an additional table:
    tblChoices:
    IDchoice, byte, (1, 2, 3, 4, 5 etc), PK
    strChoice, text, ("Strongly Agree", "Agree", etc....)
    and replace the Answer field in tblAnswer with an FK on tblChoices


    meanwhile - how to get where you want to go with your current structure??
    i guess the simplest is a whole bunch of queries, each one looking at just one question and then a report (or subreport) looking at each query. sorry that this is not a pretty solution.

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Mar 2004
    Posts
    77
    i think u will need to create 4 queries for all ur different answers.

    something like this:

    SELECT Count(TableName.[ColumnName]) AS [CountOfColumnName]
    FROM TableName
    WHERE (TableName.[ColumnName]="agree"));

    then if u want all the results in a report. create another query that queries the 4 different queries to get the different results.

    cheers.

  11. #11
    Join Date
    Oct 2004
    Posts
    31
    Quote Originally Posted by chanjw
    i think u will need to create 4 queries for all ur different answers.

    something like this:

    SELECT Count(TableName.[ColumnName]) AS [CountOfColumnName]
    FROM TableName
    WHERE (TableName.[ColumnName]="agree"));

    then if u want all the results in a report. create another query that queries the 4 different queries to get the different results.

    cheers.
    I was afraid I might have to do that. I have 27 questions, so it's a lot of work.
    I lost this thread, so have posted elsewhere, also. Sorry.

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    bad choice - table redesign was the way to go

    izy
    currently using SS 2008R2

  13. #13
    Join Date
    Oct 2004
    Posts
    31
    Quote Originally Posted by izyrider
    bad choice - table redesign was the way to go

    izy
    I haven't had a chance to look at it. So will try to look at the table redesign. I didn't want to fuss with that very much as the survey is already 'live' on company's website, and don't want to have any 'glitches'.
    Thank you for your help. I need all I can get !!!
    Regards,
    Jillian

  14. #14
    Join Date
    Oct 2004
    Posts
    31
    Finally getting back to this. I can't do much re the table structuring as this is all on an active website that I don't have direct access to. I was just wanting to print out the results, but want to know a count for each survey question that has answered with one of four options (a separate count for each option, of each question).
    Sorry. My access knowledge is only 'basic'.
    Thank you.
    Regards,
    Jillian

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Given this:

    Quote Originally Posted by Jillian
    Finally getting back to this. I can't do much re the table structuring as this is all on an active website that I don't have direct access to.
    Regards,
    Jillian
    Then this:


    SELECT Count(TableName.[ColumnName]) AS [CountOfColumnName]
    FROM TableName
    WHERE (TableName.[ColumnName]="agree"));
    Is your only viable option.

    You could also use 27 subqueries. Or maybe 27 DLookups. In any event, you'll be calculating this 27 times.

    NOW you see why izy was pushing so hard for table redesign?

    This is a classic example of why putting the effort into "fussing" with your table schema is good investment. Fuss with it a little now, or A LOT later.
    Last edited by Teddy; 01-04-05 at 15:23.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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