Help with query formatting

Hi all,
I have one table that contains, among other fields, a NAME and 14 SERVICE
fields. The ID number is obvious and the SERVICE fields contain a "Y" if
that particular person (NAME) carries out the service. A person can carry
out 1 or more services or the person can carry no service.
I am trying to write a search screen where the user will enter a partial
name and select 1 or more services to be included. The query will then
return and person who's name is LIKE the entry AND who has at least 1 of the
entered services.
I have the following: "SELECT * FROM PROVIDERS WHERE [first name] LIKE
'%TOM%' AND srv1 = 'Y' OR srv2 = 'Y' OR srv3='Y'.
What I am getting from the above is all people with TOM in their name AND
service 1. I am also getting people - no matter what their name is - with
either service 2 or service 3.
I need all people with TOM in their name and EITHER service 1, service 2 or
service 3.
Any help is appreciated.
George
George,
You need to add parentheses around the (this OR that OR other...).
The priority of boolean operators is that AND is evaluated before OR.
SELECT *
FROM PROVIDERS
WHERE [first name] LIKE '%TOM%'
AND (
srv1 = 'Y' OR srv2 = 'Y' OR srv3='Y'
)
(Ultimately, you will probably have more luck with any sizeable
application if you do not use 14 different Y/N columns. Almost
always, it is a better design to maintain a separate table to record
that information. My suggestion should get you moving forward,
but there is always the risk that it moves you forward closer to the
quicksand.)
Steve Kass
Drew University
George wrote:

>Hi all,
>I have one table that contains, among other fields, a NAME and 14 SERVICE
>fields. The ID number is obvious and the SERVICE fields contain a "Y" if
>that particular person (NAME) carries out the service. A person can carry
>out 1 or more services or the person can carry no service.
>I am trying to write a search screen where the user will enter a partial
>name and select 1 or more services to be included. The query will then
>return and person who's name is LIKE the entry AND who has at least 1 of the
>entered services.
>I have the following: "SELECT * FROM PROVIDERS WHERE [first name] LIKE
>'%TOM%' AND srv1 = 'Y' OR srv2 = 'Y' OR srv3='Y'.
>What I am getting from the above is all people with TOM in their name AND
>service 1. I am also getting people - no matter what their name is - with
>either service 2 or service 3.
>I need all people with TOM in their name and EITHER service 1, service 2 or
>service 3.
>Any help is appreciated.
>George
>