query with Count

[blizkij]

Hi

I have a table that has fields containing responses for questions Q1 through Q14. These fields contain numbers 1 through 5 or are blank. I need to query each field to count the total number of 1s, 2s, 3s etc. and total number of blanks.

Is there a way to do this through the one query? Or will I need to create a series of queries for each field?

any help would be great appreciated.

N

 

[mdthornton]

If I understand you table structure I don't think there's an elegant solution. You need to have a normalized table to do it in one go, ie question_num, response. Then you could build a group by/sum query. If you can't change your table you could simulate this with a big UNION query which pulled in individual question numbers and the response. Good Luck,
Mike T