SQL - looking for a better way.

[beginner1]

Hello,

I have a table containing questions answers (answer is between 0 and 5). I got 50 questions and for each question you need to give 3 sub answers.
Example:
3, 0, 5 - is the first question answer
1, 4, 2 - is the second answer and so on and on 50 times.
This means i got 150 lines in the table not including the
PersonID which is the main field.
The answers in the table are named this way:
Answer1a, Answer1b, Answer1c
Answer2a, Answer2b, Answer2c and on and on..
What i want to do is to check how much times the answer to each question, was 0,1,2,3,4 and 5. Don't forget that you have here 3 answers per question so i need to check how much times (count) Answer1a was 0-5. and until Answer50c...
-
I thought i'll do this with this simple SQL:
Select count(Answer1a)
From Answers
Where Answer1a = 0;
-
But this is only one! This means i need to do this SQL for each one of the 50 * 3 * 6 = 900!
I'm sure there is a better way to do this.
I hope you'll help me with this one.
Thanks.

I want to create a

 

[dschomburg]

Is this kinda what you had in mind ?

SELECT Answers.Answer1a
FROM Answers
WHERE (((Answers.Answer1a) In (0,1,2,3,4,5)));

or you could use the 'Between' clause

 

[beginner1]

This don't give me the number of times the specified answer was given.
And it still don't solve me the writing 900 SQL's for checking each one of the answers..
Sorry, that's not it, but thanks for your reply.

 

[dschomburg]

What are the fields in your table(s)?

 

[beginner1]

I got one table with fields :
ID, answer1a, answer1b, answer1c, answer2a, answer2b, answer2c, answer3a ..... until answer50c .
That's all fields i got in my table.

 

[dschomburg]

This will count the number of times the answer 0 thru 5 is given in Answer1. You would just write 2 additional queries for Answer2-3. I'm sure there is Crosstab for this but I don't know what it is.

PROVIDED THAT YOUR TABLE IS SOMETHING LIKE;

Field1 = question_num (number)
Field2 = Answer1 (number)
Field3 = Answer2 (number)
Field4 = Answer3 (number)
Field5 = PersonID (number)

Put this in your SQL

SELECT answers.answer1, Count((1)) AS CountofAnswer1
FROM answers
GROUP BY answers.answer1;

But like I said You will need to do this 2 more times. (not 899 times)

 

[beginner1]

Thanks, this really works and shows me the number of times the answer 0 thru 5 is given.
But still i a problem.
In your example you have only 3 answers, this means 3 SQL's. In my case, i got 50 questions and each answer is
built by other 3 answers. This means my table is:
Feild1 = PersonID
Feild2 = answer1a
Feild3 = answer1b
Feild4 = answer1c
Feild5 = answer2a
Feild6 = answer2b
Feild7 = answer2c
.... until
Feild151 = answer50c
-----
you see what i mean? this still won't stop me from writing lots of SQL's.
So if i do like you said:
Select answers.answer1a, Count((1)) AS CountofAnswer1a
FROM answers
GROUP BY answers.answer1a;
and then answer1a changes to answer1b and so on and on.

You saved me lots of works - from 900 SQL's to 150 which is GREAT!
But after 150 SQL's my fingers and my keyboard will be broken
If what you said is true and i didn't understand you plz don't be angry with me.

Thank You.

 

[dschomburg]

I think the issue is your table and how it's set up.
Because you have a separate field for each questions answer within a record you have 50+ fields per record.
If you just use a field to identify the question # then the fields Answer1 thru Answer3 belongs to that question. If you throw in another field such as PersonID then you increase the number of records, BUT you still only have 5 or fewer fields. Which makes 3 VS lots of queries possible.

ex:
if you had 3 records;

question_num answer1 answer2 answer3
1 5 4 2
2 4 4 2
3 3 4 3


If you had the PersonID added to this you would repeat each question num and answers. But since you don't care about the PersonID in your query, it doesn't matter how many records you have.

 

[beginner1]

Thank you! i created a questions table and answers table.
I'm doing like you told me and everything is great.
But i guess i did something wrong in the VB at the form - now, i insert first question answers, all fine.
When i add another answers, it just writes it on one of the old answers.
Another problem is in the query you gave me.
It counts only answers that has been answered. i want it to give me 0 if the answer wasn't answered. This means i'll get 6 lines in this query not matter what the answers will be.

About the first question, i wondered if i can send you the MDB file so you can check it out. This is my bigger problem becouse i can't start entering details to my database.
The query can wait.
Please leave here a message if u can help me with the MDB file.

Thanks.