Quering from multiple fields

  • Thread starter Thread starter KelleyH
  • Start date Start date
K

KelleyH

I have a table that provides multiple user choices, let
say 10. I want to query for the people that selected any
or all of the choices 1-5. I set Choice 1's "criteria" to
yes (Yes/No) and Choice 2's "or" to yes. This worked,
however when I set Choice 3's "or" to yes I lost some of
my previous records.

I figure that I'm going to have to use formula, but I'm
not sure which one.
 
Dear Kelley:

It sounds like you're working in the Query Design Grid. This
sometimes makes it more difficult to work with complex logic, but your
logic isn't all that bad.

When you need to OR a number of criteria, such as you seem to have,
each one needs to be on a separate row across the grid. That is, you
would put the criteria for the first choice on the row labelled
"Criteria:", the second one on the row labelled "or:", and the third
one on the next row down from that. You grid will allow for more than
the 5 you need, so this will work well.

The difficulty begins if you have another criteria other than these 5
choices by which you want to filter. Assume you do have this, and
that you want to see only those rows where this additional criteria is
met, along with meeting at least one of the 5 choices. Under the
column you set up for this column, you would enter the criteria in all
5 rows where you have the 5 choices set. This is, in effect, saying:

WHERE (Choice1 = "yes" and <other criteria>)
OR (Choice2 = "yes" and <other criteria>)
OR (Choice3 = "yes" and <other criteria>)
OR (Choice4 = "yes" and <other criteria>)
OR (Choice5 = "yes" and <other criteria>)

Now, if I were writing this, I would certainly use an equivalent form:

WHERE (Choice1 = "yes" OR Choice2 = "yes" OR Choice3 = "yes"
OR Choice4 = "yes" or Choice5 = "yes") AND <other criteria>

However, this cannot be described on the design grid. For really
complex logic, the grid can be a bad choice.

Anyway, I'm thinking that the "or:" on the grid confused you, making
you think that additional criteria you placed on that line would be
ORed logically. However, any 2 or more criteria placed across the
same line on the grid will be ANDed, not ORed. OR happens when you
use additional rows in the grid.

The limitations of the grid can be considerable as you get more skill
and complexity. That's one of the reasons for learning to code the
SQL yourself. Past a certain point the grid is cumbersome.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top