Microsoft Access Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMicrosoft Access Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old August 4th, 2008, 04:22 PM
jshepley jshepley is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Aug 2008
Posts: 8 jshepley User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 5 m 33 sec
Reputation Power: 0
Report | How do I show only aggregate data for survey responses?

Good afternoon!

I'm trying to develop an Access 2007 report that will allow me to analyze and aggregate raw survey Monkey survey data without displaying the individual responses to each question.

The structure of the data is as follows:
Each respondent's survey is on a separate row
Each column contains the answer to a single question
All answers are constrained to a list of choices (e.g., Best, Good, Bad, Poor)...there are no open-ended questions

What I'd like to do is to build a report that shows the percentage of responses to each question. So, for example:
Question #1: What did you think of our product?
Best: 25%
Good: 50%
Bad: 25%
Poor: 0%

Furthermore, I ask some for some demographic information at the beginning of the survey and I'd like to be able to drill down into this for each question. So, for example:
Question #1: What did you think of our product?
NY: Best: 25%, Good: 50%, Bad: 25%, Poor: 0%
NJ: Best: 10%, Good: 20%, Bad: 40%, Poor: 30%

I've spent a long time searching for help in some Access 2007 books and the MS Access 2007 online resources, I Googled other resources for a few hours today, and browsed the threads on this site, but haven't found any guidance yet on how to tackle this challenge (at least not without a lot of VBA coding). If anyone has suggestions on resources I should look at (or an idea on how to approach a solution), I would greatly appreciate it. And if there's a thread on the site on this topic that I missed, sorry for the oversight--just point me to it and I'll take it from there.

Thanks in advance for taking the time to read and consider this question...I really appreciate it!

Cheers,

Joe

Reply With Quote
  #2  
Old August 4th, 2008, 06:20 PM
dykebert's Avatar
dykebert dykebert is offline
Contributing User
Click here for more information
 
Join Date: Apr 2008
Posts: 429 dykebert User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 Days 30 m 5 sec
Reputation Power: 7
OK this is a bit long, but hopefully you can sort it out.

Without knowing the names of the fields or table I had to guess. The following query is based on the following names.

Table: SurveyData
Fields:
QuestionNum
State
Answer

You will have to substitute the appropriate names from your DB to make this work:

Code:
SELECT QuestionNum, State, 
	FormatPercent( Dcount("Answer","SurveyData","QuestionNum = " & SurveyData.QuestionNum & " AND State = """ & [State] & """ AND Answer = ""Best""")/ttl)  As Best, 
	FormatPercent( Dcount("Answer","SurveyData","QuestionNum = " &  SurveyData.QuestionNum & " AND State = """ & [State] & """ AND Answer = ""Good""")/ttl)  As Good, 
	FormatPercent( Dcount("Answer","SurveyData","QuestionNum = " & SurveyData.QuestionNum & " AND State = """ & [State] & """ AND Answer = ""Bad""")/ttl)  As Bad, 
	FormatPercent( Dcount("Answer","SurveyData","QuestionNum = " &  SurveyData.QuestionNum & " AND State = """ & [State] & """ AND Answer = ""Poor""")/ttl)  As Poor
FROM (SELECT SurveyData.QuestionNum, SurveyData.State, Count(SurveyData.Answer) AS ttl
		FROM SurveyData
		GROUP BY SurveyData.QuestionNum, SurveyData.State) As SurveyCount ;


Basically you have a query that does the count and then use that query to figure the precentages.

Creating the query that does the count stuff as a stored query will improve performance and make debugging easier.

Also this query handles the State percentages. To get just the plain question percentages you can either remove all of the state stuff from the query or create a seperate report and group just by question and use the group footer to do the totals.

Have fun!
Comments on this post
jshepley agrees: I can't give reputation points yet, but dykebert's response was super helpful--worked perfectly!
__________________
P.S. I am looking for work. <grin>.

Reply With Quote
  #3  
Old August 5th, 2008, 08:38 AM
jshepley jshepley is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Aug 2008
Posts: 8 jshepley User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 5 m 33 sec
Reputation Power: 0
Thanks for the Reply!

Dykebert,

Thanks for the quick reply--I really appreciate it!

I'm going to try this out now: I'll let you know how it goes.

Cheers, and have a great day!

Joe

Reply With Quote
  #4  
Old August 5th, 2008, 09:12 AM
jshepley jshepley is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Aug 2008
Posts: 8 jshepley User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 5 m 33 sec
Reputation Power: 0
You rock! | Follow up question

Okay, so this worked perfectly (you rock!), but I have a follow up question.

First let me provide some more info about how Survey Monkey formats the data I need to work with.

Survey Monkey provides the data from a survey as follows:
Name | State | Question 1 | Question 2 | Question 3 | Question 4 | ...

What you end up with is one row per survey response, with all the survey questions presented as separate columns, e.g.:
Joe | NY | Good | Bad | Best | Poor | ...

It seems to me that the code you sent works best if the data looks like this:
Name | State | QuestionNum | Answer

That is:
Joe | NY | 1 | Good
Joe | NY | 2 | Bad
Joe | NY | 3 | Best
Joe | NY | 4 | Poor
Bill | NJ | 1 | Best

And so on.

My question is, should I transform the Survey Monkey data to work with your query (using VB, for example), or is there a way to work with the data "as is" using another kind of query? And if both are possible, what are the pros and cons of each?

Any guidance you had would be awesome...and thanks again for taking the time to help!

Cheers!

Joe

Reply With Quote
  #5  
Old August 5th, 2008, 10:20 AM
dykebert's Avatar
dykebert dykebert is offline
Contributing User
Click here for more information
 
Join Date: Apr 2008
Posts: 429 dykebert User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 Days 30 m 5 sec
Reputation Power: 7
Hmmm I'm not coming up with a way to get what you want other than transforming the data.

You can either do the transform with VBA code into another table or you can do it with a query. Which is better depends on:

How well do you know VBA?
How many questions are there?
Is this something you need do just this once or on a regular basis?

The query that you can use to do the transform is something like this:
Code:
SELECT State, 1 as QuestionNum, [Question 1] as Answer
FROM SurveyData

UNION

SELECT State, 2, [Question 2]
FROM SurveyData

UNION

SELECT State, 3, [Question 3]
FROM SurveyData

....


then use this query as the table in the query I posted earlier.

Reply With Quote
  #6  
Old August 5th, 2008, 10:57 AM
jshepley jshepley is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Aug 2008
Posts: 8 jshepley User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 5 m 33 sec
Reputation Power: 0
Some more info on the situation

There are 70-75 questions in the survey (I have to tweak it a bit for each customer to keep it relevant), and it needs to be done on on ongoing basis (for each new customer).

I know VB somewhat, as I did a lot of work with it in a former job around text transformation (from XLS and Word to SGML and XML kind of apps). I haven't used it very much within Access other than for some very basic manipulation of form data.

So if you think VB transformation is the way to go, I'll do that for sure. Here's my idea:

I figured I would walk the spreadsheet, and for each row, first grab the key demographic data and hold it in a variable (e.g., RespondentInfo), then grab the data for the first question and hold it in another variable (e.g., SurvResponse), and then write both to a new spreadsheet structured like you suggest; second, I would grab the data for the next question and put it in SurvResponse and write it to the new spreadsheet with the same RespondentInfo. Once all the questions for that respondent were complete, I'd start all over again with the next row. And so on until the all the responses were transformed.

At that point, I figured I could import the transformed spreadsheet into Access and use the query you provided to slice and dice it.

Does this seem like a valid approach? If there's a way to do it within Access that might be more efficient, I'm all ears. I was assuming that this would be a standalone .exe that I would run outside of Access on the exported Survey Monkey data.

Thanks again for all your help so far--I really appreciate it!

Cheers!

Joe

Reply With Quote
  #7  
Old August 5th, 2008, 01:00 PM
dykebert's Avatar
dykebert dykebert is offline
Contributing User
Click here for more information
 
Join Date: Apr 2008
Posts: 429 dykebert User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 Days 30 m 5 sec
Reputation Power: 7
OK There is a more efficient method than handling it one record at a time.

First, just Link or Import the raw data spreadsheet.

Create a table to hold the transformed data.

Then use code similar to this to add the data from each question.

Code:
    Dim connDB As ADODB.Connection
    Dim miscRS As New ADODB.Recordset
    Dim qry As New Command
    Dim sql As String
    
    Dim fldCnt As Integer
    Dim ndx As Integer
    Dim questNum As Integer
    
    Set connDB = CurrentProject.Connection
    
    Set qry.ActiveConnection = connDB

    sql = "SELECT TOP 1 * FROM excelData"
    
    miscRS.Open sql, connDB
    
    fldCnt = miscRS.Fields.Count
    
    For ndx = 0 To fldCnt - 1
        If Left(miscRS.Fields(ndx).Name, 5) = "Quest" Then
            questNum = Right(miscRS.Fields(ndx).Name, 1)
            
            sql = "INSERT INTO tblSurveyData (State, QuestionNum, Answer) " & _
                    "SELECT State," & questNum & ", [" & miscRS.Fields(ndx).Name & "] " & _
                    "FROM excelData "
            qry.CommandText = sql
            qry.Execute
            
       End If
    Next
    


You will probably have to tweak the names etc to match yor situation.

I used excelData as the name of the raw Linked / Imported Excel Spreadsheet and tblSurveyData for the table to hold the transformed data.

Once you get this part sorted out, you can automate importing the spreadsheet. <grin>

Have fun!
Comments on this post
jshepley agrees: A great reply--clear and effective...and worked like a charm!

Reply With Quote
  #8  
Old August 5th, 2008, 02:38 PM
jshepley jshepley is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Aug 2008
Posts: 8 jshepley User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 5 m 33 sec
Reputation Power: 0
The best!

Dykebert,

This was perfect!

Thanks for taking the time to help me out on this and teach me something new--I totally appreciate it!

Now on to the fun part of getting this built out for my users.

Cheers!

Joe

PS - I'll keep my ears open for job openings and pass along any that I come across!

Reply With Quote
  #9  
Old February 6th, 2010, 11:30 PM
Chashywragree Chashywragree is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Feb 2010
Location: Colombia
Posts: 2 Chashywragree User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 m 24 sec
Reputation Power: 0
Send a message via ICQ to Chashywragree
forums.devarticles.com - cool

thanks! nice forum. i add forums.devarticles.com to my bookmarks

Reply With Quote
  #10  
Old March 2nd, 2010, 06:16 AM
frookson frookson is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2010
Posts: 1 frookson User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 24 m 36 sec
Reputation Power: 0
How can I do this transformation with a query in Access?

I'm new; just found this OLD response to another user that addresses my current problem.

You suggested 2 ways to transform the data (VB and query) and gave the VB solution. I do not know VB, so please advise on how to do this with an Access query.

Dykebert, I have read several of your other posts and find that your answers are exceptionally helpful.

Frookson

Quote:
Originally Posted by dykebert
OK There is a more efficient method than handling it one record at a time.

First, just Link or Import the raw data spreadsheet.

Create a table to hold the transformed data.

Then use code similar to this to add the data from each question.

Code:
    Dim connDB As ADODB.Connection
    Dim miscRS As New ADODB.Recordset
    Dim qry As New Command
    Dim sql As String
    
    Dim fldCnt As Integer
    Dim ndx As Integer
    Dim questNum As Integer
    
    Set connDB = CurrentProject.Connection
    
    Set qry.ActiveConnection = connDB

    sql = "SELECT TOP 1 * FROM excelData"
    
    miscRS.Open sql, connDB
    
    fldCnt = miscRS.Fields.Count
    
    For ndx = 0 To fldCnt - 1
        If Left(miscRS.Fields(ndx).Name, 5) = "Quest" Then
            questNum = Right(miscRS.Fields(ndx).Name, 1)
            
            sql = "INSERT INTO tblSurveyData (State, QuestionNum, Answer) " & _
                    "SELECT State," & questNum & ", [" & miscRS.Fields(ndx).Name & "] " & _
                    "FROM excelData "
            qry.CommandText = sql
            qry.Execute
            
       End If
    Next
    


You will probably have to tweak the names etc to match yor situation.

I used excelData as the name of the raw Linked / Imported Excel Spreadsheet and tblSurveyData for the table to hold the transformed data.

Once you get this part sorted out, you can automate importing the spreadsheet. <grin>

Have fun!

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft Access Development > Report | How do I show only aggregate data for survey responses?


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap