Multiple criteria query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to create a query that selects data that is like one set of criteria
but not like that criteria along with others. This is so confusing to me that
I cannot even phrase the question properly. Here is an example:

Field1 like 'A' but not like 'A' and 'B' and 'C'
 
A field can only contain one set of data at a time. Maybe you mean --
Field1 <> "A" And Field2 ="B" And Field3 = "C"

To do this open the query in design view and enter <>"A" and in the same row
but under Field2 put "B" and under Field3 put "C" to complete it.
 
I need to create a query that selects data that is like one set of criteria
but not like that criteria along with others. This is so confusing to me that
I cannot even phrase the question properly. Here is an example:

Field1 like 'A' but not like 'A' and 'B' and 'C'
Click to expand...

This is indeed confusing! Could you post an example of real data which
would be selected - and some which would not?

As phrased no records could ever be selected, because it impossible
for a value to be equal to A and also not equal to A. For that matter,
no field can ever be equal to 'A' and 'B' and 'C'. Since you're not
using wildcards in your criterion, the LIKE operator is being treated
exactly the same as the = operator.

Just a sidebar here: a query's WHERE clause in SQL must be a logical
expression which evaluates to either TRUE or FALSE. The operators
"AND" and "OR" look like the English language conjunctions, but they
really aren't: they are Boolean operators, just as + and - are
arithmatic operators. Both of them take two arguments, one on the left
and one on the right:

A AND B is True if A is a True statement and B is also True
it is False if either A or B is False

A OR B is True if A is a true statement, or if B is a true
statement, or if both are true statements
it is False if A and B are both False statements

So the expression 'A' AND 'B' will *always be true* - because any
nonzero value is True, only a numeric 0 is False.


John W. Vinson[MVP]
 
1. Normally Like should be used with wild-cards. If you don't use
wild-cards, Like is simply an (inefficient) equal comparison operator.

2. I think you set a condition that is impossible to meet: a Field value
cannot be "Like 'A'" and "Not Like 'A'" at the same time! Thus your
expression always evaluate to False (and no Record is selected???)

Describe in words what you are trying to do.
 
Sorry, I meant to include the wildcards. Can a record be like *A* but not
like *A* and *B* and *C*?

I have a field in which different letters mean different information they
ask for (A=membership, B=Maps, C=Tours). What I want to do is split out those
who ask for everything (A, B, and C) and those who ask for one or two of
these.

It's easy to select those who want all three (like *A* and like *B* and like
*C*), but I am having trouble selecting those who want any combination but
all three.

Thanks for your help.
 
Sorry, I meant to include the wildcards. Can a record be like *A* but not
like *A* and *B* and *C*?

I have a field in which different letters mean different information they
ask for (A=membership, B=Maps, C=Tours). What I want to do is split out those
who ask for everything (A, B, and C) and those who ask for one or two of
these.

It's easy to select those who want all three (like *A* and like *B* and like
*C*), but I am having trouble selecting those who want any combination but
all three.

Thanks for your help.
 
Sorry, I meant to include the wildcards. Can a record be like *A* but not
like *A* and *B* and *C*?
Click to expand...

First off, criteria do not apply to records. They apply to a single
field within a record. Secondly, each AND or OR in your query must
link LOGICAL EXPRESSIONS - *B* is not a logical expression, it's not
either true or false.

You can use AND and OR operators to connect expressions -

[Field] LIKE "*A*" AND [Field] LIKE "*B*"

for example - but the expression

[Field] LIKE "*A*" AND "*B*"

will be interpreted as comparing two expressions:

[Field] LIKE "*A*"

which might or might not be true;

and

"*B*"

which will always be TRUE, since it is not a numeric zero (FALSE is
equal to 0, any other value is TRUE).
I have a field in which different letters mean different information they
ask for (A=membership, B=Maps, C=Tours). What I want to do is split out those
who ask for everything (A, B, and C) and those who ask for one or two of
these.
Click to expand...

I still don't understand. Does this field contain values like

AC
ABC
B
BC

in different records? or what? If it does, you are violating the very
basic principle that fields should be "atomic".

What is some *ACTUAL* data from your table? I hesitate to advise since
I may be misunderstanding!


John W. Vinson[MVP]
 
Assume data entry is reasonable, e.g. A, B, C, AB, AC, BC, ABC but not
values like AAB, ACC, you can simply test for:

Len([Field1]) < 3

for those who request less than 3 items

OTOH, for database structure, it may be better to have up to 3 or more
(child) Records indicating up to 3 or more bits of info. requested by a
member.
 
Assuming the original field of up to three characters and the user wants all
A where both B and C are not in the field. I believe the criteria could be
like the criteria below. My assumption is that the three characters could
be entered in any order.

Like "*A*" AND Not (Like "*B*" and Like "*C*")

In a query,
SELECT TheField
FROM TheTable
WHERE TheField Like "*A*" AND Not (TheField Like "*B*" and TheField Like
"*C*")

If the characters were in alphabetic order then the criteria is simpler A
followed by B or C or just A and nothing else.
= "A" Or Like "A[BC]"
 
Back
Top