Multiple field query

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

Guest

For reasons that are to complecated to explain, I need to create a query that will look through multiple fields and return any rows that contain the queried item in any one of those fields.

Example:

AutoNumber ColumnA ColumnB ColumnC
1 hose Apple Orange
2 Apple Tree Cat
3 Horse Plum Grape
4 Me You Him

I want a query that would search through columns A, B and C for value "Apple" and return me lines 1 and 2

how can I achieve this?

Thanks in advance for your help!
 
SELECT TableName.ColumnA, TableName.ColumnB, TableName.ColumnB
FROM TableName
WHERE (((TableName.ColumnA)="Apple")) OR (((TableName.ColumnA)="Apple")) OR
(((TableName.ColumnA)="Apple"));



Daniel P said:
For reasons that are to complecated to explain, I need to create a query
Click to expand...
that will look through multiple fields and return any rows that contain the
queried item in any one of those fields.
Example:

AutoNumber ColumnA ColumnB ColumnC
1 hose Apple Orange
2 Apple Tree Cat
3 Horse Plum Grape
4 Me You Him

I want a query that would search through columns A, B and C for value
Click to expand...
"Apple" and return me lines 1 and 2
 
Daniel, you can do this from the query design.
1) go to the query window of the db
2) select NEW
3) for the New Query select Design View
4) from the Show Table list select the table you want
to query
5) place your cursor next to the table you selected and
right click your mouse
6) select SQL View
7) in "white" area you'll see the word SELECT and FROM
8) type in (I removed the table name from most of the
statement since all the info is coming from one table)

SELECT ColumnA, ColumnB, ColumnC
FROM TableName
WHERE (((ColumnA)="Apple")) OR (((ColumnB)="Apple")) OR
(((ColumnC)="Apple"));

9) place your cursor on the blue title bar and right
click and select Query Design

This will put you back to the design view and you'll see
how the table is being worked by the SELECT statement you
did.

Hope this is what you are looking for.
*** John
 
John,

Thanks for the help, but I still have one more question for you!

The fact of the matter is that I want the user of the query to specify what to search for. So as such, how can I modify your SQL query so that a msgbox asks the user "Search for what?" and then the returned value is used as the search parameter in the query?

Thank you once again. Your help is much appreciated.

Daniel

*****************************************
Daniel, you can do this from the query design.
1) go to the query window of the db
2) select NEW
3) for the New Query select Design View
4) from the Show Table list select the table you want
to query
5) place your cursor next to the table you selected and
right click your mouse
6) select SQL View
7) in "white" area you'll see the word SELECT and FROM
8) type in (I removed the table name from most of the
statement since all the info is coming from one table)

SELECT ColumnA, ColumnB, ColumnC
FROM TableName
WHERE (((ColumnA)="Apple")) OR (((ColumnB)="Apple")) OR
(((ColumnC)="Apple"));

9) place your cursor on the blue title bar and right
click and select Query Design

This will put you back to the design view and you'll see
how the table is being worked by the SELECT statement you
did.

Hope this is what you are looking for.
*** John
 
Daniel, it is all in the criteria of the WHERE part.
Where you see "Apple" replace it with [ColumnA Item] and a
parameter box will appear when the query runs asking for
the ColumnA Item. Or for all 3 if you replace all three.
Use the brackets. What is between the brackets is what
appears on the parameter box. So, use what you need
between the brackets. The SQL should look something like
this;

SELECT ColumnA, ColumnB, ColumnC
FROM TableName
HAVING (((ColumnA)=[ColumnA Item]))
OR (((ColumnB)=[ColumnB Item]))
OR (((ColumnC)=[ColumnC Item]));

Once you start seeing how this all develops it becomes
easier. If it's possible, it doesn't hurt to "experiment"
with queries to see how they work with different criteria,
totals, groupings, etc.

Good luck.
*** John

-----Original Message-----
John,

Thanks for the help, but I still have one more question for you!

The fact of the matter is that I want the user of the
Click to expand...
query to specify what to search for. So as such, how can
I modify your SQL query so that a msgbox asks the
user "Search for what?" and then the returned value is
used as the search parameter in the query?
 
Use the same parameter each time.

SELECT ColumnA, ColumnB, ColumnC
FROM TableName
WHERE (((ColumnA)=[Find Item]))
OR (((ColumnB)=[Find Item]))
OR (((ColumnC)=[Find Item]));

If you are doing this in the query grid, then put
[Find Item] under each field in the criteria section, but
Daniel, it is all in the criteria of the WHERE part.
Where you see "Apple" replace it with [ColumnA Item] and a
parameter box will appear when the query runs asking for
the ColumnA Item. Or for all 3 if you replace all three.
Use the brackets. What is between the brackets is what
appears on the parameter box. So, use what you need
between the brackets. The SQL should look something like
this;

SELECT ColumnA, ColumnB, ColumnC
FROM TableName
HAVING (((ColumnA)=[ColumnA Item]))
OR (((ColumnB)=[ColumnB Item]))
OR (((ColumnC)=[ColumnC Item]));

Once you start seeing how this all develops it becomes
easier. If it's possible, it doesn't hurt to "experiment"
with queries to see how they work with different criteria,
totals, groupings, etc.

Good luck.
*** John
-----Original Message-----
John,

Thanks for the help, but I still have one more question for you!

The fact of the matter is that I want the user of the
Click to expand...
query to specify what to search for. So as such, how can
I modify your SQL query so that a msgbox asks the
user "Search for what?" and then the returned value is
used as the search parameter in the query?
Thank you once again. Your help is much appreciated.

Daniel

*****************************************
Daniel, you can do this from the query design.
1) go to the query window of the db
2) select NEW
3) for the New Query select Design View
4) from the Show Table list select the table you want
to query
5) place your cursor next to the table you selected and
right click your mouse
6) select SQL View
7) in "white" area you'll see the word SELECT and FROM
8) type in (I removed the table name from most of the
statement since all the info is coming from one table)

SELECT ColumnA, ColumnB, ColumnC
FROM TableName
WHERE (((ColumnA)="Apple")) OR (((ColumnB)="Apple")) OR
(((ColumnC)="Apple"));

9) place your cursor on the blue title bar and right
click and select Query Design

This will put you back to the design view and you'll see
how the table is being worked by the SELECT statement you
did.

Hope this is what you are looking for.
*** John

.
Click to expand...
Click to expand...
 
Back
Top