Survey Data matrix (make sense of it)
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
(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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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]
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]
ASKER
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
The IF statement would be for each row (ie Dept).
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
> 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
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
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
ASKER
GUSTAV, I will think about that. I will know soon.
ASKER
I dont have any objections.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
Department
Question No and answer
Question Comment
Question No and answer
Question Comment