Simple AND query

[Helen Wheels]

I'm having a problem with an AND query and would be grateful for any
help.

The field in the table I'm querying is made up of data such as CS,HS,S
S,CA,SW
CS,S
CA,HS

I need to find fields containing CA and S so I'm using *CA* AND *S*.

However, the query is returning all fields with either CA or S
contained in them.

Any help will be gratefully received.

HW

 

[John W. Vinson]

I'm having a problem with an AND query and would be grateful for any
help.

The field in the table I'm querying is made up of data such as CS,HS,S
S,CA,SW
CS,S
CA,HS

Well, you're violating a fundamental principle of database design: fields
should be "atomic", containing only one value. If a given record is
simultaneously "S" and "CA" and "SW" then you really are embedding a one to
many relationship into one field; you'ld be better off having two tables in a
one to many relationship.

I need to find fields containing CA and S so I'm using *CA* AND *S*.

Note that

LIKE "*S*"

will be true for both HS and for SW. The wildcard cares nothing for commas;
it's looking for the literal text string S, no matter what the context.

However, the query is returning all fields with either CA or S
contained in them.

The AND in a query is not the English language conjunction. It's an operator
in Boolean Algebra: just as + is an operator in arithmatic, X AND Y is an
expression which is TRUE if both X and Y are TRUE.

If you keep your (incorrect) design, you'll need to use a query criterion such
as

WHERE "," & [yourfield] & "," LIKE "*,S,*"
AND "," & [yourfield] & "," LIKE "*,CA,*"

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Helen Wheels]

That makes perfect sense now and I could kick myself for no seeing it
before! Unfortunately the database is one set by an exam board for
GCSE students so I'll take the issue up with them....

Thanks again

HW

I'm having a problem with an AND query and would be grateful for any
help.

The field in the table I'm querying is made up of data such as CS,HS,S
S,CA,SW
CS,S
CA,HS

Well, you're violating a fundamental principle of database design: fields
should be "atomic", containing only one value. If a given record is
simultaneously "S" and "CA" and "SW" then you really are embedding a one to
many relationship into one field; you'ld be better off having two tables in a
one to many relationship.

I need to find fields containing CA and S so I'm using *CA* AND *S*.

Note that

LIKE "*S*"

will be true for both HS and for SW. The wildcard cares nothing for commas;
it's looking for the literal text string S, no matter what the context.

However, the query is returning all fields with either CA or S
contained in them.

The AND in a query is not the English language conjunction. It's an operator
in Boolean Algebra: just as + is an operator in arithmatic, X AND Y is an
expression which is TRUE if both X and Y are TRUE.

If you keep your (incorrect) design, you'll need to use a query criterion such
as

WHERE "," & [yourfield] & "," LIKE "*,S,*"
AND "," & [yourfield] & "," LIKE "*,CA,*"