Multiple Field Search Query

[Guest]

I have 9 fields in one table - key1, key2,key3 etc up to key9.

I need to create a search that will search ALL these fields via the user
entering a word and the search would retrieve all information on candidates
with THAT specific word.....

Any ideas??

Many thanks in advance.

 

[Rick B]

First, sounds like your design is flawed. You should most likely have a
one-to-many database layout, but that aside...

In the criteria under each "key" field just put something like...

=[Enter Key To Search]

Place this on a different line in your query for each key. criteria placed
on different lines creates and "or" condition, so if any of them contains
the entered key, the record would be selected.


Rick B

 

[Guest]

Rick,

Thanks for your feedback, when you say flawed, in what way do you mean....
Any help would be great. At the moment I have 9 fields on a form/table, that
the user can enter keywords about the type of work a candidate is looking for
ie: Administration, there are 9 fields, the option for more than just one
keyword.... would this be best displayed somehow else? It would need to be
connected with the candidates table/form......

Kind Regards

First, sounds like your design is flawed. You should most likely have a
one-to-many database layout, but that aside...

In the criteria under each "key" field just put something like...

=[Enter Key To Search]

Place this on a different line in your query for each key. criteria placed
on different lines creates and "or" condition, so if any of them contains
the entered key, the record would be selected.


Rick B

I have 9 fields in one table - key1, key2,key3 etc up to key9.

I need to create a search that will search ALL these fields via the user
entering a word and the search would retrieve all information on candidates
with THAT specific word.....

Any ideas??

Many thanks in advance.

 

[John Vinson]

I have 9 fields in one table - key1, key2,key3 etc up to key9.

Then you have a non-normalized, incorrectly designed table.

If you have a many to many relationship between candidates and
keywords, a much better structure would be to use THREE tables:

Candidates
CandidateID
<other info other than keywords about the candidate>

Keywords
Keyword Text <primary key>

CandidateKeywords
CandidateID
Keyword

Rather than adding nine *fields* you'ld add nine (or six, or eighteen)
*records*. Finding the candidate that matches a keyword becomes a
simple search of a single field.

I need to create a search that will search ALL these fields via the user
entering a word and the search would retrieve all information on candidates
with THAT specific word.....

If you're stuck with this "spreadsheet" design, just use OR logic: in
the query grid put

[Enter keyword:]

under all nine of the key fields, on *separate rows* of the query
grid.

John W. Vinson[MVP]

 

[Guest]

Thank you for your help, am trying create 3 tables to link as suggested.....
how can i add this to the candidate form - a subform to be inserted?

Kind regards.

I have 9 fields in one table - key1, key2,key3 etc up to key9.

Then you have a non-normalized, incorrectly designed table.

If you have a many to many relationship between candidates and
keywords, a much better structure would be to use THREE tables:

Candidates
CandidateID
<other info other than keywords about the candidate>

Keywords
Keyword Text <primary key>

CandidateKeywords
CandidateID
Keyword

Rather than adding nine *fields* you'ld add nine (or six, or eighteen)
*records*. Finding the candidate that matches a keyword becomes a
simple search of a single field.

I need to create a search that will search ALL these fields via the user
entering a word and the search would retrieve all information on candidates
with THAT specific word.....

If you're stuck with this "spreadsheet" design, just use OR logic: in
the query grid put

[Enter keyword:]

under all nine of the key fields, on *separate rows* of the query
grid.

John W. Vinson[MVP]

 

[Guest]

I have created only 1 table - candidatekeywords... which connect to
candidates table by candidateID, this seems to work okay. The user can enter
in keywords via a subform on the candidate form. I am able to undertake a
single keyword search ie [enter keyword] of the users choice, however how
would i undertake multiple keyword search ie should the user enter in 2
keywords, i would like candidates with BOTH or EITHER to be retrived.....

Any help would be great... thanks.

 

[John Vinson]

Thank you for your help, am trying create 3 tables to link as suggested.....
how can i add this to the candidate form - a subform to be inserted?

Exactly. A Continuous Subform based on the CandidateKeywords table;
use the CandidateID as the master/child link field to keep the subform
in synch, and put a Combo Box as the only control on the subform. It
should be bound to the Keyword field and based on a query
(alphabetically sorted) of the Keywords table - this will have the
added benefit of controlling the keyword vocabulary so you don't end
up having to search for "Access" and "Acces" and "MS Access" and
"Desktop databases" and "Desktop database" as keywords for the same
skill.

John W. Vinson[MVP]

 

[John Vinson]

I have created only 1 table - candidatekeywords... which connect to
candidates table by candidateID, this seems to work okay. The user can enter
in keywords via a subform on the candidate form. I am able to undertake a
single keyword search ie [enter keyword] of the users choice, however how
would i undertake multiple keyword search ie should the user enter in 2
keywords, i would like candidates with BOTH or EITHER to be retrived.....

A simple OR clause would do here: a criterion on
CandidateKeywords.Keyword of

"Access" OR "SQL/Server"

Better, use parmeter queries:

[Enter a keyword:] OR ([Enter another:] OR [Enter another:] IS NULL)

better still, use a Form to enter several keywords and some VBA code
to construct the SQL of a query (that's in the advanced lesson <G>).

John W. Vinson[MVP]

 

[Guest]

Thank you so much!! That has helped a great deal!
I still have so much learn!!!!!

I have created only 1 table - candidatekeywords... which connect to
candidates table by candidateID, this seems to work okay. The user can enter
in keywords via a subform on the candidate form. I am able to undertake a
single keyword search ie [enter keyword] of the users choice, however how
would i undertake multiple keyword search ie should the user enter in 2
keywords, i would like candidates with BOTH or EITHER to be retrived.....

A simple OR clause would do here: a criterion on
CandidateKeywords.Keyword of

"Access" OR "SQL/Server"

Better, use parmeter queries:

[Enter a keyword:] OR ([Enter another:] OR [Enter another:] IS NULL)

better still, use a Form to enter several keywords and some VBA code
to construct the SQL of a query (that's in the advanced lesson <G>).

John W. Vinson[MVP]

 

[Guest]

John,

SELECT CandidateKeywords.Keyword, CandidateKeywords.CandidateID
FROM CandidateKeywords
WHERE [Enter a Keyword] Or ([Enter another] Or [Enter another] Or [Enter
another] Or [Enter another] Is Null);

This is the query I have created, however it only allows me to enter 2
keywords... and no more... any help on this??

Kind regards

I have created only 1 table - candidatekeywords... which connect to
candidates table by candidateID, this seems to work okay. The user can enter
in keywords via a subform on the candidate form. I am able to undertake a
single keyword search ie [enter keyword] of the users choice, however how
would i undertake multiple keyword search ie should the user enter in 2
keywords, i would like candidates with BOTH or EITHER to be retrived.....

A simple OR clause would do here: a criterion on
CandidateKeywords.Keyword of

"Access" OR "SQL/Server"

Better, use parmeter queries:

[Enter a keyword:] OR ([Enter another:] OR [Enter another:] IS NULL)

better still, use a Form to enter several keywords and some VBA code
to construct the SQL of a query (that's in the advanced lesson <G>).

John W. Vinson[MVP]

 

[John Vinson]

John,

SELECT CandidateKeywords.Keyword, CandidateKeywords.CandidateID
FROM CandidateKeywords
WHERE [Enter a Keyword] Or ([Enter another] Or [Enter another] Or [Enter
another] Or [Enter another] Is Null);

This is the query I have created, however it only allows me to enter 2
keywords... and no more... any help on this??

If you have the same prompt multiple times it will ask only once. If
you want true flexibility in the number of keywords, you'll need a
different approach, using VBA code to take the user's input on an
unbound Form and actually constructing the SQL view of the query. Try
looking at http://www.mvps.org/access or the other websites referenced
therein for "query by form" examples.

John W. Vinson[MVP]

 

[Guest]

Hi. your idea works, however, if I have 9 keys, then the user will be
prompted 9 times and have to enter the same string 9 times.

I want my query to search all 9 keys at once using one criteria.
for example: [Enter name]
i should only have to enter the name once and the query should search all 9
keys and return all records with that name in any one of the 9 fields.

you're help woudl be appreciated.

Thanks.
Ryan

First, sounds like your design is flawed. You should most likely have a
one-to-many database layout, but that aside...

In the criteria under each "key" field just put something like...

=[Enter Key To Search]

Place this on a different line in your query for each key. criteria placed
on different lines creates and "or" condition, so if any of them contains
the entered key, the record would be selected.


Rick B

I have 9 fields in one table - key1, key2,key3 etc up to key9.

I need to create a search that will search ALL these fields via the user
entering a word and the search would retrieve all information on candidates
with THAT specific word.....

Any ideas??

Many thanks in advance.

 

[Duane Hookom]

Sounds like your table needs to be normalized. However you can use the same
parameter prompt on multiple lines under multiple fields in the query design
grid. You will be prompted only once.

--
Duane Hookom
MS Access MVP

Hi. your idea works, however, if I have 9 keys, then the user will be
prompted 9 times and have to enter the same string 9 times.

I want my query to search all 9 keys at once using one criteria.
for example: [Enter name]
i should only have to enter the name once and the query should search all
9
keys and return all records with that name in any one of the 9 fields.

you're help woudl be appreciated.

Thanks.
Ryan

First, sounds like your design is flawed. You should most likely have a
one-to-many database layout, but that aside...

In the criteria under each "key" field just put something like...

=[Enter Key To Search]

Place this on a different line in your query for each key. criteria
placed
on different lines creates and "or" condition, so if any of them contains
the entered key, the record would be selected.


Rick B

I have 9 fields in one table - key1, key2,key3 etc up to key9.

I need to create a search that will search ALL these fields via the
user
entering a word and the search would retrieve all information on candidates
with THAT specific word.....

Any ideas??

Many thanks in advance.

 

[Guest]

Thanks for the tip. I have made the change.
However, because my parameters are different lines they are "OR" parameters.
I have 15 fields where I enter drug results. So my first criteria is [Enter
Drug Name 1], and this criteria is under each field on seperate lines (so
user is prompted only once). then again on seperate lines I have my next
criteria [Enter Drug Name 2]
and so forth for a third drug. However, with this query, I get all the
records with Drug 1 and all records with drug 2. but I only want the records
that have both Drug 1 and Drug 2]. So it's not filtering for both drugs, but
for both drugs separately. It doesn't matter if the record has 7 drugs, but
if that record has drug 1 AND drug 2 in any 2 of the 7 fields, the query
should only grab that record. I hope this makes sense. Similarly if I'm
looking ONLY for the records that have drug 1, drug 2 AND drug 3 in any of
those 15 fields.
I'm now learning access so you're help would be apprecitated.
Thanks. Ryan

Sounds like your table needs to be normalized. However you can use the same
parameter prompt on multiple lines under multiple fields in the query design
grid. You will be prompted only once.

--
Duane Hookom
MS Access MVP

Hi. your idea works, however, if I have 9 keys, then the user will be
prompted 9 times and have to enter the same string 9 times.

I want my query to search all 9 keys at once using one criteria.
for example: [Enter name]
i should only have to enter the name once and the query should search all
9
keys and return all records with that name in any one of the 9 fields.

you're help woudl be appreciated.

Thanks.
Ryan

First, sounds like your design is flawed. You should most likely have a
one-to-many database layout, but that aside...

In the criteria under each "key" field just put something like...

=[Enter Key To Search]

Place this on a different line in your query for each key. criteria
placed
on different lines creates and "or" condition, so if any of them contains
the entered key, the record would be selected.


Rick B


I have 9 fields in one table - key1, key2,key3 etc up to key9.

I need to create a search that will search ALL these fields via the
user
entering a word and the search would retrieve all information on
candidates
with THAT specific word.....

Any ideas??

Many thanks in advance.

 

[Duane Hookom]

I wouldn't go any further until the records could be normalized. Multiple
fields (15) over complicates your application.

Consider the Northwind [Order Details] with OrderID and ProductID. Let's say
you want to find a single order that contained both of two different
products (10 and 12). The SQL would be:

SELECT OrderID, Count(OrderID) AS NumOf
FROM [Order Details]
WHERE ProductID In (10,12)
GROUP BY OrderID
HAVING Count(OrderID)=2;

This query returns the one orderID 11077 which contained the two products. I
could change the criteria to prompt for two ProductID values:

SELECT OrderID, Count(OrderID) AS NumOf
FROM [Order Details]
WHERE ProductID In ([Enter ProductID 1],[Enter ProductID 2])
GROUP BY OrderID
HAVING Count(OrderID)=2;

This is so much simpler than attempting to find the same with multiple
repeating fields.

You might be able to use a UNION query to normalize your table and then
query your union query to find where both drugs are present.


--
Duane Hookom
MS Access MVP

Thanks for the tip. I have made the change.
However, because my parameters are different lines they are "OR"
parameters.
I have 15 fields where I enter drug results. So my first criteria is
[Enter
Drug Name 1], and this criteria is under each field on seperate lines (so
user is prompted only once). then again on seperate lines I have my next
criteria [Enter Drug Name 2]
and so forth for a third drug. However, with this query, I get all the
records with Drug 1 and all records with drug 2. but I only want the
records
that have both Drug 1 and Drug 2]. So it's not filtering for both drugs,
but
for both drugs separately. It doesn't matter if the record has 7 drugs,
but
if that record has drug 1 AND drug 2 in any 2 of the 7 fields, the query
should only grab that record. I hope this makes sense. Similarly if I'm
looking ONLY for the records that have drug 1, drug 2 AND drug 3 in any of
those 15 fields.
I'm now learning access so you're help would be apprecitated.
Thanks. Ryan

Sounds like your table needs to be normalized. However you can use the
same
parameter prompt on multiple lines under multiple fields in the query
design
grid. You will be prompted only once.

--
Duane Hookom
MS Access MVP

Hi. your idea works, however, if I have 9 keys, then the user will be
prompted 9 times and have to enter the same string 9 times.

I want my query to search all 9 keys at once using one criteria.
for example: [Enter name]
i should only have to enter the name once and the query should search
all
9
keys and return all records with that name in any one of the 9 fields.

you're help woudl be appreciated.

Thanks.
Ryan


:

First, sounds like your design is flawed. You should most likely have
a
one-to-many database layout, but that aside...

In the criteria under each "key" field just put something like...

=[Enter Key To Search]

Place this on a different line in your query for each key. criteria
placed
on different lines creates and "or" condition, so if any of them
contains
the entered key, the record would be selected.


Rick B


I have 9 fields in one table - key1, key2,key3 etc up to key9.

I need to create a search that will search ALL these fields via the
user
entering a word and the search would retrieve all information on
candidates
with THAT specific word.....

Any ideas??

Many thanks in advance.

 

[Guest]

thank you very much. sounds complicated, but I'll give it a try. I'm still
learning this stuff. What do you mean by normalizing the records. and where
do I enter this SQL? in my query?

Ryan

I wouldn't go any further until the records could be normalized. Multiple
fields (15) over complicates your application.

Consider the Northwind [Order Details] with OrderID and ProductID. Let's say
you want to find a single order that contained both of two different
products (10 and 12). The SQL would be:

SELECT OrderID, Count(OrderID) AS NumOf
FROM [Order Details]
WHERE ProductID In (10,12)
GROUP BY OrderID
HAVING Count(OrderID)=2;

This query returns the one orderID 11077 which contained the two products. I
could change the criteria to prompt for two ProductID values:

SELECT OrderID, Count(OrderID) AS NumOf
FROM [Order Details]
WHERE ProductID In ([Enter ProductID 1],[Enter ProductID 2])
GROUP BY OrderID
HAVING Count(OrderID)=2;

This is so much simpler than attempting to find the same with multiple
repeating fields.

You might be able to use a UNION query to normalize your table and then
query your union query to find where both drugs are present.


--
Duane Hookom
MS Access MVP

Thanks for the tip. I have made the change.
However, because my parameters are different lines they are "OR"
parameters.
I have 15 fields where I enter drug results. So my first criteria is
[Enter
Drug Name 1], and this criteria is under each field on seperate lines (so
user is prompted only once). then again on seperate lines I have my next
criteria [Enter Drug Name 2]
and so forth for a third drug. However, with this query, I get all the
records with Drug 1 and all records with drug 2. but I only want the
records
that have both Drug 1 and Drug 2]. So it's not filtering for both drugs,
but
for both drugs separately. It doesn't matter if the record has 7 drugs,
but
if that record has drug 1 AND drug 2 in any 2 of the 7 fields, the query
should only grab that record. I hope this makes sense. Similarly if I'm
looking ONLY for the records that have drug 1, drug 2 AND drug 3 in any of
those 15 fields.
I'm now learning access so you're help would be apprecitated.
Thanks. Ryan

Sounds like your table needs to be normalized. However you can use the
same
parameter prompt on multiple lines under multiple fields in the query
design
grid. You will be prompted only once.

--
Duane Hookom
MS Access MVP

Hi. your idea works, however, if I have 9 keys, then the user will be
prompted 9 times and have to enter the same string 9 times.

I want my query to search all 9 keys at once using one criteria.
for example: [Enter name]
i should only have to enter the name once and the query should search
all
9
keys and return all records with that name in any one of the 9 fields.

you're help woudl be appreciated.

Thanks.
Ryan


:

First, sounds like your design is flawed. You should most likely have
a
one-to-many database layout, but that aside...

In the criteria under each "key" field just put something like...

=[Enter Key To Search]

Place this on a different line in your query for each key. criteria
placed
on different lines creates and "or" condition, so if any of them
contains
the entered key, the record would be selected.


Rick B


I have 9 fields in one table - key1, key2,key3 etc up to key9.

I need to create a search that will search ALL these fields via the
user
entering a word and the search would retrieve all information on
candidates
with THAT specific word.....

Any ideas??

Many thanks in advance.

 

[Duane Hookom]

Jeff Conrad has lots of links regarding normalization at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101.

This method requires you to enter the SQL into the SQL view of a query.

--
Duane Hookom
MS Access MVP

thank you very much. sounds complicated, but I'll give it a try. I'm still
learning this stuff. What do you mean by normalizing the records. and
where
do I enter this SQL? in my query?

Ryan

I wouldn't go any further until the records could be normalized. Multiple
fields (15) over complicates your application.

Consider the Northwind [Order Details] with OrderID and ProductID. Let's
say
you want to find a single order that contained both of two different
products (10 and 12). The SQL would be:

SELECT OrderID, Count(OrderID) AS NumOf
FROM [Order Details]
WHERE ProductID In (10,12)
GROUP BY OrderID
HAVING Count(OrderID)=2;

This query returns the one orderID 11077 which contained the two
products. I
could change the criteria to prompt for two ProductID values:

SELECT OrderID, Count(OrderID) AS NumOf
FROM [Order Details]
WHERE ProductID In ([Enter ProductID 1],[Enter ProductID 2])
GROUP BY OrderID
HAVING Count(OrderID)=2;

This is so much simpler than attempting to find the same with multiple
repeating fields.

You might be able to use a UNION query to normalize your table and then
query your union query to find where both drugs are present.


--
Duane Hookom
MS Access MVP

Thanks for the tip. I have made the change.
However, because my parameters are different lines they are "OR"
parameters.
I have 15 fields where I enter drug results. So my first criteria is
[Enter
Drug Name 1], and this criteria is under each field on seperate lines
(so
user is prompted only once). then again on seperate lines I have my
next
criteria [Enter Drug Name 2]
and so forth for a third drug. However, with this query, I get all the
records with Drug 1 and all records with drug 2. but I only want the
records
that have both Drug 1 and Drug 2]. So it's not filtering for both
drugs,
but
for both drugs separately. It doesn't matter if the record has 7 drugs,
but
if that record has drug 1 AND drug 2 in any 2 of the 7 fields, the
query
should only grab that record. I hope this makes sense. Similarly if
I'm
looking ONLY for the records that have drug 1, drug 2 AND drug 3 in any
of
those 15 fields.
I'm now learning access so you're help would be apprecitated.
Thanks. Ryan

:

Sounds like your table needs to be normalized. However you can use the
same
parameter prompt on multiple lines under multiple fields in the query
design
grid. You will be prompted only once.

--
Duane Hookom
MS Access MVP

Hi. your idea works, however, if I have 9 keys, then the user will
be
prompted 9 times and have to enter the same string 9 times.

I want my query to search all 9 keys at once using one criteria.
for example: [Enter name]
i should only have to enter the name once and the query should
search
all
9
keys and return all records with that name in any one of the 9
fields.

you're help woudl be appreciated.

Thanks.
Ryan


:

First, sounds like your design is flawed. You should most likely
have
a
one-to-many database layout, but that aside...

In the criteria under each "key" field just put something like...

=[Enter Key To Search]

Place this on a different line in your query for each key.
criteria
placed
on different lines creates and "or" condition, so if any of them
contains
the entered key, the record would be selected.


Rick B


I have 9 fields in one table - key1, key2,key3 etc up to key9.

I need to create a search that will search ALL these fields via
the
user
entering a word and the search would retrieve all information on
candidates
with THAT specific word.....

Any ideas??

Many thanks in advance.