search multiple fields at once

C

Chuck216

Is it possible and if so how? I have 3 fields in the same query that I want
to search with the same value input from the user. I want the user to only
have to input the search criteria once and have it search the 3 fields.

Thanks for the help in advance.
 
O

Ofer

All you need is to put the same parameter for all three fields

Select * From TableName Where Field1 = [Enter Parameter] And Field2 = [Enter
Parameter] And Field3 = [Enter Parameter]

It will prompt the user only once for the [Enter Parameter]
 
T

Tom Wickerath

Hi Chuck,

Yes, this is possible, but it indicates that you may not have a properly
normalized database structure. You should not be storing similar information
into three fields.

As a short term solution, you can use a union query to produce a single
column recordset, which you then use as the basis for your search. A union
query can only be created using the SQL View. Create a new query and select
the appropriate table. In query design, click on View > SQL View. Enter a SQL
statement similar to the following, making the appropriate substitutions for
the names of the fields and the table name:

SELECT [Field1] AS Entity
WHERE [Field1] IS NOT NULL
FROM [TableName]
UNION
SELECT [Field2] AS Entity
WHERE [Field2] IS NOT NULL
FROM [TableName]
UNION
SELECT [Field3] AS Entity
WHERE [Field3] IS NOT NULL
FROM [TableName]
ORDER BY Entity

Save this query. You can use this query as the source for another query, or
you can add a parameter prompt directly to this query. Note that the result
is a read only recordset. The square brackets are required if your field
and/or table names include spaces or reserved words in Access/JET.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Is it possible and if so how? I have 3 fields in the same query that I want
to search with the same value input from the user. I want the user to only
have to input the search criteria once and have it search the 3 fields.

Thanks for the help in advance.
 
T

Tom Wickerath

Oops...I switched the order of the WHERE and FROM clauses. If you want to use
the union query, use this form instead:

SELECT [Field1] AS Entity
FROM [TableName]
WHERE [Field1] IS NOT NULL
UNION
SELECT [Field2] AS Entity
FROM [TableName]
WHERE [Field2] IS NOT NULL
UNION
SELECT [Field3] AS Entity
FROM [TableName]
WHERE [Field3] IS NOT NULL
ORDER BY Entity



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Chuck,

Yes, this is possible, but it indicates that you may not have a properly
normalized database structure. You should not be storing similar information
into three fields.

As a short term solution, you can use a union query to produce a single
column recordset, which you then use as the basis for your search. A union
query can only be created using the SQL View. Create a new query and select
the appropriate table. In query design, click on View > SQL View. Enter a SQL
statement similar to the following, making the appropriate substitutions for
the names of the fields and the table name:

SELECT [Field1] AS Entity
WHERE [Field1] IS NOT NULL
FROM [TableName]
UNION
SELECT [Field2] AS Entity
WHERE [Field2] IS NOT NULL
FROM [TableName]
UNION
SELECT [Field3] AS Entity
WHERE [Field3] IS NOT NULL
FROM [TableName]
ORDER BY Entity

Save this query. You can use this query as the source for another query, or
you can add a parameter prompt directly to this query. Note that the result
is a read only recordset. The square brackets are required if your field
and/or table names include spaces or reserved words in Access/JET.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Is it possible and if so how? I have 3 fields in the same query that I want
to search with the same value input from the user. I want the user to only
have to input the search criteria once and have it search the 3 fields.

Thanks for the help in advance.
 
J

John Vinson

Is it possible and if so how? I have 3 fields in the same query that I want
to search with the same value input from the user. I want the user to only
have to input the search criteria once and have it search the 3 fields.

Thanks for the help in advance.
Click to expand...

In the query grid put the search criterion under each of the three
fields, on SEPARATE LINES of the query grid. I presume you're using

[Enter search term:]

as the parameter for the criterion. The SQL view would have

.... WHERE [Field1] = [Enter search term:] OR [Field2] = [Enter search
term:] OR [Field3] = [Enter search term:]


The need to do this does set off some alarm bells in my mind: if these
fields are named, say, Keyword1, Keyword2, Keyword3 or are otherwise
repeating fields, you may need to consider instead having two tables
in a one-to-many relationship. "Fields are expensive, records are
cheap"!

John W. Vinson[MVP]
 
T

TedMi

Ofer's method will find records which have the *SAME* information in *ALL* of
the fields searched. But I suspect that what you really want is to find
records that have the requested information in *ANY ONE* of the fields
searched. If so, you have a badly designed, unnormalized database. You should
not be searching for a value present in any one of a number of fields -
that's not how relational database are meant to be used. Yes, you can write
such a query for a given number of fields, but because the number of
instances of data can change, the query will fail as soon as you increase the
number of fields in which your searched-for value can appear. Please look up
the topic of normalization in any basic text on relational database design.
Alternately, you can post here the structure of the table you want to search,
and you will get lots of advice on how to normalize it. But: blindly
following someone else's advice is no substitute for understanding the
principles of databases.
 
O

Ofer

à have few quries that have the same criteria for few fields, and it doesn't
mean that my DB is "badly designed, unnormalized database".
All queries join few tables, one table is the parent that contain a status,
and it join with a child table that contain a status also, both status has
the same value, so if I want the resault of open status in both tables, then
the criteria will be the same.

So, I wouldn't jump and say that the DB is "badly designed, unnormalized
database", you can suggest the answer for the question, and include your
point of view, just as John Vinson did.
 
Top