Database Background: I have a database that stores customer survey response data in three tables by Line of Business. The data that is entered includes survey date, ratings type questions(ex: 1-5, yes/no), and additional comments for each question. All the data that can be entered for a single LOB survey response(ratings, comments, date) is stored in one record on one table. I am using an autonumber and LOB identifier as a composite key.
I am beginning to wonder if maybe I setup my database improperly, but let me explain further.
I have no problem pulling aggregate data by date period for the ratings type questions, even accounting for the fact that some respondents are not answering all questions.
Problem: I want to be able to pull all the comments for a LOB into a report grouped by question. I can do this, but am getting alot of white space due to some respondents not filling in comments. CanShrink doesn't seem to be doing the trick either.
This is what my report currently looks like:
Question 1:
"Service was really good"
"Thanks for the wonderful service"
(blank)
"The rep was rude"
(blank)
"I would like it if I could complete my transaction via the web"
(blank)
(blank)
I would like to see this:
Question 1:
"Service was really good"
"Thanks for the wonderful service"
"The rep was rude"
"I would like it if I could complete my transaction via the web"
Question 2:
......
I want to be able to select and display only Not Null comments by question, and am beginning to think this must be accomplished at the query level. Keep in mind there are 5 different comments fields, and some respondents will write comments for all questions, some questions, or no questions. I've tried building queries on top of queries but get record source join errors. I am not sure if a Union query will do the trick either.
Any ideas are much appreciated!
I am beginning to wonder if maybe I setup my database improperly, but let me explain further.
I have no problem pulling aggregate data by date period for the ratings type questions, even accounting for the fact that some respondents are not answering all questions.
Problem: I want to be able to pull all the comments for a LOB into a report grouped by question. I can do this, but am getting alot of white space due to some respondents not filling in comments. CanShrink doesn't seem to be doing the trick either.
This is what my report currently looks like:
Question 1:
"Service was really good"
"Thanks for the wonderful service"
(blank)
"The rep was rude"
(blank)
"I would like it if I could complete my transaction via the web"
(blank)
(blank)
I would like to see this:
Question 1:
"Service was really good"
"Thanks for the wonderful service"
"The rep was rude"
"I would like it if I could complete my transaction via the web"
Question 2:
......
I want to be able to select and display only Not Null comments by question, and am beginning to think this must be accomplished at the query level. Keep in mind there are 5 different comments fields, and some respondents will write comments for all questions, some questions, or no questions. I've tried building queries on top of queries but get record source join errors. I am not sure if a Union query will do the trick either.
Any ideas are much appreciated!
