Query Criteria: How to handle >9 "or" conditions?

[Jimbo213]

Using Access2003-SP2 with XP:

I have a form with 12 yes-no fields.
I'd like to build a report using a query to those 12 fields so that the
query qualifies any row where the OR condition is met.

For example:
YN01 = -1 or
YN02 = -1 or
YN03 = -1 or
....
YN12 = -1

The query builder stops at YN09 - apparently an Access limit.

In SQLView it shows the WHERE clause having 9 "or" conditions.

How can I get 12 conditions?

 

[John Spencer]

If you mean the design view stops at 9 lines of criteria, you can add more
criteria lines by selecting Insert Rows from the menu.

Alternative is
Field: YN01 + YN02 + YN03 ...+ YN12
Criteria: < 0

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Chris O'C via AccessMonster.com]

You wouldn't have this problem if the table were normalized. All of those
YN## fields should be in a single field and the response in another field.
Like this:

UserID Question Answer
1 YN01 -1
1 YN02 -1
1 YN03 0
1 YN04 -1
etc.

SELECT abs(sum(Answer)) as YesAnswers
FROM tablename
WHERE (UserID = 1) and (Question IN ('YN01', 'YN02', 'YN03', 'YN04'))

Query result:

YesAnswers
3

With this design, you could have 4 questions or thousands of questions, but
you don't have to keep adding a new field for each new question. You just
need 1 new row identifying the user, naming which question, and the response.

Chris
Microsoft MVP

 

[Jimbo213]

I "think" I understand.

1) how would I create a form that triggers this query. The form I created
had the 12 checkboxes. What would the form look like w/ your solution. I'm
not getting it - sorry for being so dense.

2) how would the query return all the rows where YN1 = -1 OR YN2 = -1 OR YN3
= -1? I want the query result to show [for instance] 15 rows meeting these
OR conditions, not just the value 3.

 

[Chris O'C via AccessMonster.com]

You're not being dense. It's just hard to picture this concept if you've
never seen it before.

If you restructure your table similar to what I described in my first post,
you can create a query that displays the data you want, then use the form
wizard to easily create a form that displays the data.

A very simple version of the query would look like this:

SELECT Question
FROM tablename
WHERE Answer = -1
ORDER BY Question

This will show all questions where the answer was yes. (None of the no
answers would show.) Use the form wizard to create a form in datasheet view
so that it looks like the query results in datasheet view -- each record
right below the others in a vertical fashion.

If you want to only show a certain person's yes answers, you need to also
identify which person in the where clause of the query. That query might
look like this:

SELECT Question
FROM tablename
WHERE (UserID = 35) and (Answer = -1)
ORDER BY Question

The query results would show only User #35's yes answers.

Chris
Microsoft MVP

I "think" I understand.

1) how would I create a form that triggers this query. The form I created
had the 12 checkboxes. What would the form look like w/ your solution. I'm
not getting it - sorry for being so dense.

2) how would the query return all the rows where YN1 = -1 OR YN2 = -1 OR YN3
= -1? I want the query result to show [for instance] 15 rows meeting these
OR conditions, not just the value 3.

You wouldn't have this problem if the table were normalized. All of those
YN## fields should be in a single field and the response in another field.

[quoted text clipped - 41 lines]