Query limitations in Access '97

[TMurray]

Hi, I have a database with 30 "checkbox" fields. If a customer is unhappy about something a check goes into that field. At the end of the day most of these "checkboxes" are empty. Question; how can I query this data so that I get only the fields with a checkmark? I tried adding "Yes" to the criteria in the query and that works fine, but I can only do that for 9 fields. I've tried seperating it into 4 different queries but keep getting stuck (I couldn't write a report incorporating all 4 queries).

Any suggestions would be greatly appreciated.

Thanks in advance,
Tom

 

[dk]

Hi,

It seems as though you may have an odd table structure. Do you have an individual field for each question i.e. a field per checkbox?

An alternative way would be to have a table which has two fields - one containing the question and one containing the answer. A possibly more efficient way is to store your questions in one table and results in another. Here is an example for your questions table:


<pre>tblQuestions
QuestionID QuestionText
1 Did you like your meal?
2 Did you like you wine?
3 Did you like the service?
</pre>

Then you could have an Answers table.

<pre>
tblAnswers
ResponderName QuestionID Answer
Dave 1 Yes
Dave 2 No
Dave 3 Yes
John 1 Yes
John 2 Yes
John 3 No
Paul 1 No
Paul 2 No
Paul 3 Yes
</pre>

You'd then have no problems creating a query including only certain answers.

EDIT - Sorry about the column headers being screwed