Solved

Survey Data matrix (make sense of it)

Posted on 2015-01-24
15
62 Views
Last Modified: 2016-02-14
Experts,

(I can accept a solution to this question in either Access or Excel)

I have the attached data from a download off of a survey company.
The data is difficult to read.
I am trying to make sense of it.  
There is a total of 5 questions.
Each department has answered their assigned questions as either True or False.

I need to show in easy to read format the answers and comments for each Department.
If there is a Null then that means that the dept was not assigned that question.  

for example, the dept Project Finance was assigned Question 1 and Question 3 as those questions are the only ones out of 5 questions that this dept was assigned as there is a True or False for only those questions.    
I think that if you see the attached excel it will make more sense (hopefully) as describing it is a little difficult.

Please let me know how to present this data in a more easily read format.  
I tried a pivot but that doesnt seem to work.  

fyi:  I have truncated the data to only 5 questions.  The original data has hundreds of questions.  

thank you
GizmoDataEE.xlsx
0
Comment
Question by:pdvsa
  • 7
  • 2
  • 2
  • +2
15 Comments
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 166 total points
ID: 40568777
how to present this data in a more easily read format.
"easy to read" may mean different things to different people...
To me, the data is already easy to read...
Can you post an example of your expected output?

For example:
Do you just want this data reformatted?
Do you want summaries?
Is this output to be fed to another system?
Is this output for presentation only, or do you want to allow edits?

The original data has hundreds of questions.
Hundreds?
Two hundred is "hundreds, ...but 9 hundred is also "hundreds"
In Access you have a limit of 255 columns.
So you will have to give us a rough idea of how many Hundreds, (and will this number increase)

JeffCoachman
0
 

Author Comment

by:pdvsa
ID: 40568829
Jeff, an example:
Department
          Question No and answer
           Question Comment
           Question No and answer
            Question Comment
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 166 total points
ID: 40568942
Are you sure you can't get the data in another format? Perhaps for a fee? The Excel sheet is certainly not the original format.

If you have more than 127 questions, you can't even link the sheet as is because of the field count limit of 255.
If so, you would have to create Named Ranges for groups of questions or all questions, and then link these - a big job to do either manually or by writing some code to create the Named Ranges.

/gustav
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 40568947
I would have three tables - one for departments  (primary key DepartmentID), one for questions (primary key QuestionID), and one for Responses:
    ResponseID (autonumber primary key)
    ResponseDepartment (the department answering the question)
    ResponseQuestion (the question being answered)
    ResponseResult (the answer to the question - probably a numeric field to be interpreted as required)
    ResponseComment (a free-form text field)

Loading the database from the source spreadsheets is easy - it only requires a loop which generates an INSERT INTO SQL  statement to be executed for each question.

This method gives you all the advantages of a properly normalised database - notably ease of query design.

Graham Mandeno [Access MVP 1996-2015]
0
 

Author Comment

by:pdvsa
ID: 40569233
Graham, thank you.  I can conceptualize how to do what you describe.  My only issue is the LOOP code to partition the questions per each dept.  

Fyi: I do have a table with depts and a field [QuestionAssigned], number property.  I imagine this table is quite helpful in solving my issue.  However, linking this table is an issue as I see it because the only field that has a question number in the excel source data is the actual question (format is "Question #.  "Followed by the question").  I think if I could somehow break out the question number I can see a solution more clearly.


Maybe you or another expert has an idea how to break out the question number into a separate column or another idea if after reading my response.

Thank you
0
 

Author Comment

by:pdvsa
ID: 40594808
I think I am going to have to delete this question.  I will ask another.  I dont know what else to do.  I dont know how to make send of this report in access and possibly excel will work.  
I am not sure if some sort of  match/offsett in excel would work for this.  
I can not get the data in any other format from where I am getting it now (I want to avoid editing the data)

Let me know if there are any follow ups.
0
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 168 total points
ID: 40595196
Hello pdvsa

I'm sorry, I missed your previous response and so did not follow up in a more timely fashion.

Having looked at your sample spreadsheet, and given that there are "hundreds of questions",  your spreadsheet is not suitable for either linking or importing to an Access database, because it will have (2n+1) columns (where n is the number of questions) and an Access table has a limit of 255 columns (fields).

You will therefore need to use some VBA code and some Excel automation to import the data into Access.

I am going to make some assumptions:
Row 1 of your spreadsheet contains column headings; "Dept Name", "Question 1 : <Question 1 text>", "Question 1 Comments", "Question 2 : <Question 2 text>", "Question 2 Comments",  ... etc
Column A of your spreadsheet contains the department name
Columns B, D, F, H. etc contain either Null (if the question was not assigned) or the text "TRUE" or "FALSE", which is that department's response to question 1, 2, 3, 4, etc
Columns C, E, G, I, etc contains the comments (not required) pertaining to the answer on the left

What you need to do is use VBA code to:
Open the spreadsheet
Read row 1 and populate the Questions table with the question number and the question text
For each following row, read column A and populate the Departments table (if a record does not already exist), then for each following pair of columns, if the first column has a value then create a record in Responses with the DepartmentID, the QuestionNumber, the response, and the comment
To display the results, simply create an Access report based on a query joining all three of your tables.  Group the report by DepartmentName, and for each Responses record, list the question number, the text, the response and the comment.

-- Graham
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:pdvsa
ID: 40596133
Graham, thank you.  That's a little out of my league to be frank with you.  I was thinking that maybe an excel formula with a combination of if and for each cell that is not null return the corresponding column heading.  Do you follow me?  Let me know thank you once again.
0
 

Author Comment

by:pdvsa
ID: 40596136
The IF statement would be for each row (ie Dept).
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40596141
Did you check this out:

> Are you sure you can't get the data in another format? Perhaps for a fee?
> The Excel sheet is certainly not the original format.

/gustav
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40597069
pdvsa,

In the end, ...I don't think there will be an *easy* way to "make sense" of this data.
What makes sense to one person, may be utterly confusing to another.

What Graham posted would be the way to go if you wanted to "Normalize" this data.

To me, I see creating some sort of Pivot/Summary.

Ultimately you will have to ask your superiors what they would like, then work form there.

JeffCoachman
0
 

Author Comment

by:pdvsa
ID: 40597621
GUSTAV, I will think about that.  I will know soon.
0
 

Author Comment

by:pdvsa
ID: 40657437
I dont have any objections.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40665996
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now