If Then Else Statements

[Laura]

I have created a dbase for a survey asking respondents to
rate 15 different questions with a rating of 1-5 (5 being
best). In the table are fields for each of the 15
questions in the survey (field names are Q1, Q2, Q3,
etc.). Entered in the survey (table) are responses
(ratings) for each question.
I am wanting to create a report that takes ONLY responses
with a 3 or higher rating for EACH question. So say that
Q1 someone rated it a 4, but that same person rated Q2 a
1, I want to keep the data from Q1 (since it's 3 or
higher) and NOT Q2 (because it's less than 3).
I have been trying to design a query that would say "If
Q1 is greater than 2, leave that number, else indicate 0"
and have that same criteria for EACH question in the
query. Is this possible?

I appreciate anyone's expertise and assistance!

 

[Michel Walsh]

Hi,


UPDATE myTable
SET q1=iif(q1>=3, q1, 0),
q2=iif(q2>=3, q2, 0),
...
ad nauseam.


You may try to normalize your data, with a structure like:

RecordID, QuestionNumber, Answer
xxx, 1, 5
xxx, 2, 2
xxx, 3, 1
....
xxx, xx, 4 ' data sample

Instead of

RecordID, Q1, Q2, Q3, ..., Qxx
xxx 5 2 1 ... 4



the query would have been just:


UPDATE myTable SET Answer=iif(Answer>=3, Answer, 0)


and you would have not developed a carpatian syndrome.

Hoping it may help,
Vanderghast, Access MVP

Design your TABLE to make the work easy, design the FORM to make the
end-user interactions easy... DO NOT necessary design your TABLES as if they
were FORMS.

 

[Yvonneb]

In the query builder you can use IIF(expression,true,false)
so...
IIF(Q1 > 2,Q1,0)