Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Aug 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Survey Results (2002)

    I have a database that collects various customer service results from an online form written in ASP. I need to take the data, now in Access, and be able to tally results. The form asks the client a question and then provides them with a drop down box, "satisfied, very satisfied, not satisfied, very unsatisfied" once they have completed all the drop down questions hitting submit sends the data to an mdb. From here I need to complie data by saying how many "satisfieds" there were, how many "Unsatisfieds" etc and display it in a report. Is there an easy way to do this or should I just muddle my way through. I am access literate but not savvy [img]/forums/images/smilies/smile.gif[/img] Any help or direction appreciated.

    K

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Survey Results (2002)

    First a question, are the results Satisfied saved as "Satisfied" or a 1 or 0 based on the selection made.

    In either event, there are various ways to do this. Assuming that all of the data resides in one table and the actual words, i.e, "satisfied" are used, and you want to tally your results on a form, you could do the following:

    - Create a query that gets all unique values of where the data = Satisfied. (I.e,. Select * from table where field = Satisfied)
    - On a Form, Create a textbox with a Dcount Function such as DCount("[Field]","Query Name")

    When you open the form, the DCount will provide the total number of records = Satisfied.
    Do the same for the other fields.
    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Lounger
    Join Date
    Aug 2002
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Survey Results (2002)

    In the Query do I have to put individual field names in the place of "field" or is there a wildcard I can use to state all. I have about 50 different fields it need to look at and determine the "satisfied" or "not satisfied" which is how they are used, rather than 1 or 0 as you asked. Thanks for your help!

    (I.e,. Select * from table where field = Satisfied)

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Survey Results (2002)

    You are in for a big query if you have used 50 fields to collect the answers.

    You could split the data (the 50 questions) out into another table as a many (new table) to one (original table) relationship.

    Having done this it's a simple query you would then end up with as you would just have to sum one table.

    What are the field names (of the 50 questions), there may be a simple solution to split the table if it's decided that this is the way to go.

    Anybody else have ideas?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Survey Results (2002)

    There may be a simpler solution, but I would create a separate grouping query for each field.
    Each would count the number of customerid's (or whtever the key field is) when grouped by the values of that field.
    The queries would tell you what you want.
    If you want to present all this in a report, you could build one report, with a searate subreport based on each grouping query.

    To build each query, select the key, and one other field, click the Totals button on the toolbar, and groupby will appear in each field of the query. In the key field change this groupby to count.
    Regards
    John



  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Survey Results (2002)

    Do you want a count of the "satisfied"s for each question, or do you want a total count of all "satisfied"s for all questions?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Survey Results (2002)

    I am assuming that if you have asked 50 questions you want 50 answers. Otherwise why ask the questions. Also you have given users 4 options "very satisfied" down to "very unsatisfied". Do you want the number in each category, or are you collapsing them to just satisfied and unsatisfied. (why give them 4 options if you are only interested in two.)
    I have another idea on how to do this that avoids creating lots of queries.
    Create a recordset using dao, and define an 50 x 4 array. Then loop through each field and each record in the recordset, and increment the relevant array variable for each variable, depending on the value of the field.

    To display the results you would need an unbound report, in which you could dynamically assign the value of the array variables to controls on the report.,
    I have a project where this technique might be useful, so i will try to get it to work.
    Regards
    John



  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Survey Results (2002)

    The way that I do this is on the path of Patt's suggestion. Having a table that holds each of the 50 reponses as different fields is going to be cumbersome, as well as not properly normalized. I have one table for the general Survey identifying data, a second table for Responses and a third table for Question information (QuestionID and the QuestionStatement). Their relationships are one to many. So the Response table has just 3 fields, SurveyID, QuestionID and the Response itself. You can calculate statistics from this table much more easily. I believe that I once posted a structure for this type of application on the forum. If you do a search on "Survey", you will find it.

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Survey Results (2002)

    You said you are going to put this in a report. You could try to create the report based on the table with the data and set the grouping options to Client and/or other fields and sum each of the fields, i.e., satisfied, not satisfied, etc... Then remove all of the erroneous data except the sums to get the totals. Since you base the data in true text, i.e. satisfied, you could base the report on a query that changes the data from satisfied to a 1 (for summing purposes) or adds in the necessary fields that can be used to counted. (Hope I am making sense.)

    I think this will work and is fairly simple to do. If you need more help, post your database and we can provide you with an example.

    Good Luck
    Regards,

    Gary
    (It's been a while!)

  10. #10
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Survey Results (2002)

    If you got this technique to work, I'd be very interested in having a look.

    Elizabeth

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Survey Results (2002)

    I attach a sample with it working.
    My survey has 10 questions each with five possible answers , coded as 0 to 4.
    The report counts the number of responses for each question.

    This sample is set up to make the coding as simple as possible. If the responses were words rather than numbers, you could do something similar, but would need a select case statement to work out which value of the array to increment.

    Also needs some error checking. A validation rule on the table would be one approach.
    Attached Files Attached Files
    Regards
    John



Posting Permissions

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