OK,

I have a survey with 33 questions. The possible answers are: 4, 3, 2.5, 2 and 1
The Access DB has a bunch of columns, including q1, q2, q3...q33

For the report I created a Recordset/Query that looks like this, but soon discovered a BIG problem with the WHERE clause: (it should only count if it sees the number 4 in a particular column)


SELECT COUNT(q1) as cnt1_4, COUNT(q2) as cnt2_4, COUNT(q3) as cnt3_4, COUNT(q4) as cnt4_4, COUNT(q5) as cnt5_4, COUNT(q6) as cnt6_4, COUNT(q7) as cnt7_4, COUNT(q8) as cnt8_4, COUNT(q9) as cnt9_4, COUNT(q10) as cnt10_4, COUNT(q11) as cnt11_4, COUNT(q12) as cnt12_4, COUNT(q13) as cnt13_4, COUNT(q14) as cnt14_4, COUNT(q15) as cnt15_4, COUNT(q16) as cnt16_4, COUNT(q17) as cnt17_4, COUNT(q18) as cnt18_4, COUNT(q19) as cnt19_4, COUNT(q20) as cnt20_4, COUNT(q21) as cnt21_4, COUNT(q22) as cnt22_4, COUNT(q23) as cnt23_4, COUNT(q24) as cnt24_4, COUNT(q25) as cnt25_4, COUNT(q26) as cnt26_4, COUNT(q27) as cnt27_4, COUNT(q28) as cnt28_4, COUNT(q29) as cnt29_4, COUNT(q30) as cnt30_4, COUNT(q31) as cnt31_4, COUNT(q32) as cnt32_4, COUNT(q33) as cnt33_4
FROM surveys
WHERE course_code = 'MMColParam' AND q1 = 4 OR q2 = 4

This WHERE clause will NOT work, seeing that it would look for a 4 in column q1 or q2, so I stopped and am trying to figure it out.

The problem I have is how to tell the query to ONLY count if it sees a 4 in it's respective q1, q2, q3...q33 column.

Creating a Recordset for each possibility would take 165 seperate queries and that sounds STUPID.

My brain is fried from looking at this for hours...PLEASE help?

I figured it out.

Mitch

When in doubt...reboot!