Fill In The Blanks

[GrexP]

I have a database with survey results. For instance, 10 companies each had their customers fill out a survey with 10 questions on it. Each company turned in anywhere from 50 to 100 surveys. All of the data is stored in a table and then 2 reports are printed. One report shows the responses broken down by company and then the other report is all responses. The survey answers are stored as a value of 0 to 4.

I store the answers like this
ComanyIndex - Integer
QuestIndex – Integer
QuestValue - Integer

Here is the problem: Let’s say Company Number 1 had 50 surveys turned in. For question one, though, no one selected answer number 0 or answer number 4. When I print the report it does not reflect that no one selected those answers. The report prints like this

1. How was the customer service?

OK - 15 – 30%
Good - 25 – 50%
Very Good - 10 – 20%

What I would like it to look like is

Poor – 0 - 0 %
OK - 15 – 30%
Good - 25 – 50%
Very Good - 10 – 20%
Excellent – 0 – 0%

I want all questions to show 5 results even if no data was entered for some of the values. How would I do this?

Thanks

 

[boblarson]

In the Query by example grid, make your query an Outer join by joining the table which has the description of the values with the values table and then right-click on the join line and make sure the selection:

Select all records in "table with description name here" and only those records which match in table "values table name here"

 

[GrexP]

Some thing's not working. I've tried every way to express the join and I'm not getting nulls for the missing spots. Could there be something with the table design?

 

[boblarson]

Some thing's not working. I've tried every way to express the join and I'm not getting nulls for the missing spots. Could there be something with the table design?

Could be, any chance you can post your db?

 

[GrexP]

Unfortunately, I can't do that. It is very odd, though. I can get a query that will show only the missing response (I'm testing one question from one company), and I can write a query that shows found responses, but I can't get them to work together.

 

[boblarson]

Unfortunately, I can't do that. It is very odd, though. I can get a query that will show only the missing response (I'm testing one question from one company), and I can write a query that shows found responses, but I can't get them to work together.

Post the SQL from your queries and maybe the SQL experts can help.