Searching mutiple fields with the same string

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

Guest

I'm really stuck. Within my database I have two related tables. I would like
to search within 6 fields of one table and pull back any hits in terms of a
product stored in a related table. I've tried this and it only seems to give
back data relating from the hits in the first field (Reativity.Specificity).
Any good ideas will be gratefully received. Thanks

SELECT Antibodies.ProductName
FROM Reactivity INNER JOIN Antibodies ON
Reactivity.Specificity=Antibodies.Specificity
WHERE Reactivity.Specificity Like [Search for?] Or Reactivity.Alt_Name1 Like
[Search for?] Or Reactivity.Alt_Name2 Like [Search for?] Or
Reactivity.Alt_Name3 Like [Search for?] Or Reactivity.Alt_Name4 Like [Search
for?] Or Reactivity.Alt_Name5 Like [Search for?] Or Reactivity.Alt_Name6 Like
[Search for?];
 
In a nutshell, your table isn't normalized properly as evidenced by the
Alt_Name fields. What happens to all your queries, forms, and reports when
you need to add Alt_Name7?

You need a third table to handle all the Alt_Name's. I suggest getting some
relational database training or reading "Database Design for Mere Mortals" by
Hernandez before proceeding any further on this database.
 
Since you have set up a join on Specificity the only records that will get
returned are the ones that match specificity.

You MIGHT be able to get what you want by using no join (a cartesian join)

SELECT Antibodies.ProductName
FROM Reactivity , Antibodies
WHERE
(
Reactivity.Specificity Like [Search for?]
Or Reactivity.Alt_Name1 Like [Search for?]
Or Reactivity.Alt_Name2 Like [Search for?]
Or Reactivity.Alt_Name3 Like [Search for?]
Or Reactivity.Alt_Name4 Like [Search for?]
Or Reactivity.Alt_Name5 Like [Search for?]
Or Reactivity.Alt_Name6 Like [Search for?]
)
And Antibodies.Specificity Like [Search for?]

This appears to be a case where you might want to consider a redesign of
your table structure. An additional table for all the repeating fields
would seem to be in order, so you could store one to any number of alternate
names for each record in the reactivity table.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
OR you might get what you want using a subquery in the where clause WHICH
should be close to the fastest method.

SELECT Antibodies.ProductName
FROM Reactivity INNER JOIN Antibodies
ON Reactivity.Specificity=Antibodies.Specificity
WHERE Reactivity.Specificity IN
(
SELECT R.Specificity
FROM Reactivity as R
WHERE R.Specificity Like [Search for?]
Or R.Alt_Name1 Like [Search for?]
Or R.Alt_Name2 Like [Search for?]
Or R.Alt_Name3 Like [Search for?]
Or R.Alt_Name4 Like [Search for?]
Or R.Alt_Name5 Like [Search for?]
Or R.Alt_Name6 Like [Search for?]
)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John Spencer said:
Since you have set up a join on Specificity the only records that will get
returned are the ones that match specificity.

You MIGHT be able to get what you want by using no join (a cartesian join)

SELECT Antibodies.ProductName
FROM Reactivity , Antibodies
WHERE
(
Reactivity.Specificity Like [Search for?]
Or Reactivity.Alt_Name1 Like [Search for?]
Or Reactivity.Alt_Name2 Like [Search for?]
Or Reactivity.Alt_Name3 Like [Search for?]
Or Reactivity.Alt_Name4 Like [Search for?]
Or Reactivity.Alt_Name5 Like [Search for?]
Or Reactivity.Alt_Name6 Like [Search for?]
)
And Antibodies.Specificity Like [Search for?]

This appears to be a case where you might want to consider a redesign of
your table structure. An additional table for all the repeating fields
would seem to be in order, so you could store one to any number of
alternate names for each record in the reactivity table.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

BossFan said:
I'm really stuck. Within my database I have two related tables. I would
like
to search within 6 fields of one table and pull back any hits in terms of
a
product stored in a related table. I've tried this and it only seems to
give
back data relating from the hits in the first field
(Reativity.Specificity).
Any good ideas will be gratefully received. Thanks

SELECT Antibodies.ProductName
FROM Reactivity INNER JOIN Antibodies ON
Reactivity.Specificity=Antibodies.Specificity
WHERE Reactivity.Specificity Like [Search for?] Or Reactivity.Alt_Name1
Like
[Search for?] Or Reactivity.Alt_Name2 Like [Search for?] Or
Reactivity.Alt_Name3 Like [Search for?] Or Reactivity.Alt_Name4 Like
[Search
for?] Or Reactivity.Alt_Name5 Like [Search for?] Or Reactivity.Alt_Name6
Like
[Search for?];
Click to expand...
Click to expand...
 
Thanks for the advice - I may consider revising the database and I appreciate
the potential limitations of the Alt_Name fields but I am looking at data
spanning 15 years or more and there have never been more than 4 Alt_names in
common usage. (However I do appreciate that this is not a good reason for
poor design!)

Jerry Whittle said:
In a nutshell, your table isn't normalized properly as evidenced by the
Alt_Name fields. What happens to all your queries, forms, and reports when
you need to add Alt_Name7?

You need a third table to handle all the Alt_Name's. I suggest getting some
relational database training or reading "Database Design for Mere Mortals" by
Hernandez before proceeding any further on this database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


BossFan said:
I'm really stuck. Within my database I have two related tables. I would like
to search within 6 fields of one table and pull back any hits in terms of a
product stored in a related table. I've tried this and it only seems to give
back data relating from the hits in the first field (Reativity.Specificity).
Any good ideas will be gratefully received. Thanks

SELECT Antibodies.ProductName
FROM Reactivity INNER JOIN Antibodies ON
Reactivity.Specificity=Antibodies.Specificity
WHERE Reactivity.Specificity Like [Search for?] Or Reactivity.Alt_Name1 Like
[Search for?] Or Reactivity.Alt_Name2 Like [Search for?] Or
Reactivity.Alt_Name3 Like [Search for?] Or Reactivity.Alt_Name4 Like [Search
for?] Or Reactivity.Alt_Name5 Like [Search for?] Or Reactivity.Alt_Name6 Like
[Search for?];
Click to expand...
Click to expand...
 
Thanks for giving this some thought. From your response and others I can see
that the design of the current database is poor and I will consider a
redesign. If this tip works - I'll give it a try tomorrow - it will be
adequate for my needs. I really appreciate your insight.

John Spencer said:
OR you might get what you want using a subquery in the where clause WHICH
should be close to the fastest method.

SELECT Antibodies.ProductName
FROM Reactivity INNER JOIN Antibodies
ON Reactivity.Specificity=Antibodies.Specificity
WHERE Reactivity.Specificity IN
(
SELECT R.Specificity
FROM Reactivity as R
WHERE R.Specificity Like [Search for?]
Or R.Alt_Name1 Like [Search for?]
Or R.Alt_Name2 Like [Search for?]
Or R.Alt_Name3 Like [Search for?]
Or R.Alt_Name4 Like [Search for?]
Or R.Alt_Name5 Like [Search for?]
Or R.Alt_Name6 Like [Search for?]
)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John Spencer said:
Since you have set up a join on Specificity the only records that will get
returned are the ones that match specificity.

You MIGHT be able to get what you want by using no join (a cartesian join)

SELECT Antibodies.ProductName
FROM Reactivity , Antibodies
WHERE
(
Reactivity.Specificity Like [Search for?]
Or Reactivity.Alt_Name1 Like [Search for?]
Or Reactivity.Alt_Name2 Like [Search for?]
Or Reactivity.Alt_Name3 Like [Search for?]
Or Reactivity.Alt_Name4 Like [Search for?]
Or Reactivity.Alt_Name5 Like [Search for?]
Or Reactivity.Alt_Name6 Like [Search for?]
)
And Antibodies.Specificity Like [Search for?]

This appears to be a case where you might want to consider a redesign of
your table structure. An additional table for all the repeating fields
would seem to be in order, so you could store one to any number of
alternate names for each record in the reactivity table.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

BossFan said:
I'm really stuck. Within my database I have two related tables. I would
like
to search within 6 fields of one table and pull back any hits in terms of
a
product stored in a related table. I've tried this and it only seems to
give
back data relating from the hits in the first field
(Reativity.Specificity).
Any good ideas will be gratefully received. Thanks

SELECT Antibodies.ProductName
FROM Reactivity INNER JOIN Antibodies ON
Reactivity.Specificity=Antibodies.Specificity
WHERE Reactivity.Specificity Like [Search for?] Or Reactivity.Alt_Name1
Like
[Search for?] Or Reactivity.Alt_Name2 Like [Search for?] Or
Reactivity.Alt_Name3 Like [Search for?] Or Reactivity.Alt_Name4 Like
[Search
for?] Or Reactivity.Alt_Name5 Like [Search for?] Or Reactivity.Alt_Name6
Like
[Search for?];
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use Same Parameter For Different Fields Within Query? 1
Searching for best matches in string query 5
Field search for text 3
Keyword search in Access 1
HOW CAN I CREATE A MULTIPLE FIELD SEARCH BOX? 3
How can I create a multiple field search box? (corrected post) 2
Query w/ 1 parameter - 16 different fields, same possible answer 5
Search Multiple Fields From Same String 6

Back
Top