multiple search query

[wspintern]

Im using Access 2003 and a novice user. Here is what Im trying to accomplish...

I have an inventory control table titled (Standards) and in this table there
are three columns titled: Keyword 1, Keyword 2, Keyword 3. I want to be able
to create a search query so that I can search each of these columns based on
the information in them. However, some of the words appear in Keyword 1
column as they do in Keyword 2, and 3 (for example: the word 'sedative'
appears in both but not for the same drug standard as this would be further
down the list for another standard). So is there a way that I can search the
three columns for the same thing? Also is there a way to create a multiple
search...so that I can search for more than one word across the three columns?

Thank you very much for your help in advance...I am a novice but Im trying
to learn so any help is much appreciated. I even have the 2003 Access Bible
for reference!

 

[mscertified]

Its easier (for me) to illustrate this in SQL rather than the query builder.

Search for one value in multiple columns:
SELECT * FROM Standards WHERE Keyword1 = 'something' OR Keyword2 =
'something' OR Keyword3 = 'something'

Search for multiple values in multiple columns:
SELECT * FROM Standards WHERE Keyword1 IN ('A','B','C') OR Keyword2 IN
('A','B','C') OR Keyword3 IN ('A','B','C')

Dorian

 

[wspintern]

Thanks for your help...so far I just get a bunch of syntax errors and have no
idea how to fix it. Any ideas?

 

[KARL DEWEY]

A couple of options --
1 - Take it apart and put it back together a little at a time, testing after
adding something new.
2 - Post your query SQL by opening it in design view, click on menu VIEW -
SQL View, hightlight all, copy, and paste in a post. Let others look at it
and suggest revisions.

 

[wspintern]

Well I have tried to take things apart and try a little at a time but I guess
I just dont understand the SQL well enough to figure it out so I have pasted
everything in its original form to see if anyone can help figure this out!
Thanks so very much for your help!

 

[KARL DEWEY]

Try working in design view as it gives you a graphical view - also known as
GUI - Graphical User Interface. It writes the syntax for you.

 

[wspintern]

Ok I have and this is where I am at so far: Right now as far as searching
for one term in multiple colums I am able to only get things that pertain to
one column instead things that pertain to all three (for ex= I type in
'sedative' as a search term which is present in both columns 'Keyword 1' and
'Keyword 2' yet I only get results from the 'Keyword 1' column and nothing
from the other column. Not sure what to do? Here is the SQL data:
SELECT tbl_Standards.[Standard_ #], tbl_Standards.Chemical_name,
tbl_Standards.[Keyword 1], tbl_Standards.[Keyword 2], tbl_Standards.[Keyword
3]
FROM tbl_Standards
WHERE (((tbl_Standards.[Keyword 1])=[keyword1])) OR
(((tbl_Standards.[Keyword 2])=[keyword2]))OR (((tbl_Standards.[Keyword
3])=[keyword3]));

Thanks!

 

[KARL DEWEY]

Try this ---
SELECT tbl_Standards.[Standard_ #], tbl_Standards.Chemical_name,
tbl_Standards.[Keyword 1], tbl_Standards.[Keyword 2], tbl_Standards.[Keyword
3]
FROM tbl_Standards
WHERE (((tbl_Standards.[Keyword 1])=[Enter keyword])) OR
(((tbl_Standards.[Keyword 2])=[Enter keyword])) OR (((tbl_Standards.[Keyword
3])=[Enter keyword]));


--
KARL DEWEY
Build a little - Test a little

Ok I have and this is where I am at so far: Right now as far as searching
for one term in multiple colums I am able to only get things that pertain to
one column instead things that pertain to all three (for ex= I type in
'sedative' as a search term which is present in both columns 'Keyword 1' and
'Keyword 2' yet I only get results from the 'Keyword 1' column and nothing
from the other column. Not sure what to do? Here is the SQL data:
SELECT tbl_Standards.[Standard_ #], tbl_Standards.Chemical_name,
tbl_Standards.[Keyword 1], tbl_Standards.[Keyword 2], tbl_Standards.[Keyword
3]
FROM tbl_Standards
WHERE (((tbl_Standards.[Keyword 1])=[keyword1])) OR
(((tbl_Standards.[Keyword 2])=[keyword2]))OR (((tbl_Standards.[Keyword
3])=[keyword3]));

Thanks!

Try working in design view as it gives you a graphical view - also known as
GUI - Graphical User Interface. It writes the syntax for you.

 

[wspintern]

Almost!! SO close...when I type the word 'sedative' in the first search box
that pops up it shows the results for both columns...but when I try a
different word say 'opiate' it will only show the results for the second
column and not the first or third. So its very close but not quite! Thanks
for your help so far!

Try this ---
SELECT tbl_Standards.[Standard_ #], tbl_Standards.Chemical_name,
tbl_Standards.[Keyword 1], tbl_Standards.[Keyword 2], tbl_Standards.[Keyword
3]
FROM tbl_Standards
WHERE (((tbl_Standards.[Keyword 1])=[Enter keyword])) OR
(((tbl_Standards.[Keyword 2])=[Enter keyword])) OR (((tbl_Standards.[Keyword
3])=[Enter keyword]));


--
KARL DEWEY
Build a little - Test a little

Ok I have and this is where I am at so far: Right now as far as searching
for one term in multiple colums I am able to only get things that pertain to
one column instead things that pertain to all three (for ex= I type in
'sedative' as a search term which is present in both columns 'Keyword 1' and
'Keyword 2' yet I only get results from the 'Keyword 1' column and nothing
from the other column. Not sure what to do? Here is the SQL data:
SELECT tbl_Standards.[Standard_ #], tbl_Standards.Chemical_name,
tbl_Standards.[Keyword 1], tbl_Standards.[Keyword 2], tbl_Standards.[Keyword
3]
FROM tbl_Standards
WHERE (((tbl_Standards.[Keyword 1])=[keyword1])) OR
(((tbl_Standards.[Keyword 2])=[keyword2]))OR (((tbl_Standards.[Keyword
3])=[keyword3]));

Thanks!

Try working in design view as it gives you a graphical view - also known as
GUI - Graphical User Interface. It writes the syntax for you.
--
KARL DEWEY
Build a little - Test a little


:

Well I have tried to take things apart and try a little at a time but I guess
I just dont understand the SQL well enough to figure it out so I have pasted
everything in its original form to see if anyone can help figure this out!
Thanks so very much for your help!

:

A couple of options --
1 - Take it apart and put it back together a little at a time, testing after
adding something new.
2 - Post your query SQL by opening it in design view, click on menu VIEW -
SQL View, hightlight all, copy, and paste in a post. Let others look at it
and suggest revisions.
--
KARL DEWEY
Build a little - Test a little


:

Thanks for your help...so far I just get a bunch of syntax errors and have no
idea how to fix it. Any ideas?

:

Its easier (for me) to illustrate this in SQL rather than the query builder.

Search for one value in multiple columns:
SELECT * FROM Standards WHERE Keyword1 = 'something' OR Keyword2 =
'something' OR Keyword3 = 'something'

Search for multiple values in multiple columns:
SELECT * FROM Standards WHERE Keyword1 IN ('A','B','C') OR Keyword2 IN
('A','B','C') OR Keyword3 IN ('A','B','C')

Dorian

:

Im using Access 2003 and a novice user. Here is what Im trying to accomplish...

I have an inventory control table titled (Standards) and in this table there
are three columns titled: Keyword 1, Keyword 2, Keyword 3. I want to be able
to create a search query so that I can search each of these columns based on
the information in them. However, some of the words appear in Keyword 1
column as they do in Keyword 2, and 3 (for example: the word 'sedative'
appears in both but not for the same drug standard as this would be further
down the list for another standard). So is there a way that I can search the
three columns for the same thing? Also is there a way to create a multiple
search...so that I can search for more than one word across the three columns?

Thank you very much for your help in advance...I am a novice but Im trying
to learn so any help is much appreciated. I even have the 2003 Access Bible
for reference!