SQL. keyword search in 3 tables.

T

Tommy

Hi All,

I need to run any keyword search on the 3 tables, each table has one
specific field containing the information. For example, I type in "test" in
the search box/query, it will search all 3 fields in 3 different tables, and
provide me the hits if any. can you please help? thanks. I have the SQL
below, but I have to put in the same keyword three times in order to get the
hits, is there a way to type once and get the result?

SELECT Attorneys3.*, Attorneys3.PracticeArea
FROM Attorneys3
WHERE ((Attorneys3.PracticeArea) Like "*" & [Enter Search for Attorney3
Table] & "*");
UNION
SELECT Attorneys2.*,Attorneys2.PracticeArea
FROM Attorneys2
WHERE (((Attorneys2.PracticeArea) Like "*" & [Enter Search for Attorney2
Table] & "*"));
UNION
SELECT Attorneys1.*,Attorneys1.PracticeArea
FROM Attorneys1
WHERE (((Attorneys1.PracticeArea) Like "*" & [Enter Search for Attorney1
Table] & "*"));
 
P

pietlinden

Hi All,

I need to run any keyword search on the 3 tables, each table has one
specific field containing the information.  For example, I type in "test" in
the search box/query, it will search all 3 fields in 3 different tables, and
provide me the hits if any.  can you please help? thanks.  I have theSQL
below, but I have to put in the same keyword three times in order to get the
hits, is there a way to type once and get the result?

SELECT Attorneys3.*, Attorneys3.PracticeArea
FROM Attorneys3
WHERE ((Attorneys3.PracticeArea) Like "*" & [Enter Search for Attorney3
Table] & "*");
UNION
SELECT Attorneys2.*,Attorneys2.PracticeArea
FROM Attorneys2
WHERE (((Attorneys2.PracticeArea) Like "*" & [Enter Search for Attorney2
Table] & "*"));
UNION
SELECT Attorneys1.*,Attorneys1.PracticeArea
FROM Attorneys1
WHERE (((Attorneys1.PracticeArea) Like "*" & [Enter Search for Attorney1
Table] & "*"));
Click to expand...

Why do you have 3 apparently identical tables in your database?
That's the root of your problem.

To answer your question, though... the easiest way to do this may be
to create a textbox on an unbound form and then point your queries at
it.

SELECT Attorneys1.*,Attorneys1.PracticeArea
FROM Attorneys1
WHERE (((Attorneys1.PracticeArea) Like "*" & Forms!
[MyUnboundOPENFormName]![txtSearchForMe] & "*"));
 
J

John Spencer

Did you try the following? Note that all three prompts are identical and that
the trailing semicolons have been removed

SELECT Attorneys3.*, Attorneys3.PracticeArea
FROM Attorneys3
WHERE ((Attorneys3.PracticeArea) Like "*" &
[Enter Search for Attorney Table] & "*")
UNION
SELECT Attorneys2.*,Attorneys2.PracticeArea
FROM Attorneys2
WHERE (((Attorneys2.PracticeArea) Like "*" &
[Enter Search for Attorney Table] & "*"))
UNION
SELECT Attorneys1.*,Attorneys1.PracticeArea
FROM Attorneys1
WHERE (((Attorneys1.PracticeArea) Like "*" &
[Enter Search for Attorney Table] & "*"));


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
D

Dale Fye

How about:

SELECT U.* FROM (
SELECT "Attorneys3" as Source, Attorneys3.*
FROM Attorneys3
UNION
SELECT "Attorneys2" as Source, Attorneys2.*
FROM Attorneys2
UNION
SELECT "Attorneys1" as Source, Attorneys1.*
FROM Attorneys1) as U
WHERE U.PracticeArea Like "*" & [Enter Search for Attorney] & "*"

As stated in the previous response, not sure why you think you need three
identical (I assume they are identical or the UNION query won't work
properly) tables. Better method is to have a single table and a field to
identify the attorney#.

Also, in the above query, use of UNION instead of UNION ALL will cause
ACCESS to delete all of the duplicates. You might want to consider using
"UNION ALL" instead. This would be significantly faster.
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
T

Tommy

Thanks John! That WORKED.

May I ask you one more question please.

I have seen someone can put a hypelink in one of fields for each record on
the result query table, click the link of the corresponding record, it opens
that record in the form view instead of table gridline view.

The reason for three tables because it is for three different entity, each
entity has it's own attorneys and practice area, as well as other different
fields.


John Spencer said:
Did you try the following? Note that all three prompts are identical and that
the trailing semicolons have been removed

SELECT Attorneys3.*, Attorneys3.PracticeArea
FROM Attorneys3
WHERE ((Attorneys3.PracticeArea) Like "*" &
[Enter Search for Attorney Table] & "*")
UNION
SELECT Attorneys2.*,Attorneys2.PracticeArea
FROM Attorneys2
WHERE (((Attorneys2.PracticeArea) Like "*" &
[Enter Search for Attorney Table] & "*"))
UNION
SELECT Attorneys1.*,Attorneys1.PracticeArea
FROM Attorneys1
WHERE (((Attorneys1.PracticeArea) Like "*" &
[Enter Search for Attorney Table] & "*"));


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi All,

I need to run any keyword search on the 3 tables, each table has one
specific field containing the information. For example, I type in "test" in
the search box/query, it will search all 3 fields in 3 different tables, and
provide me the hits if any. can you please help? thanks. I have the SQL
below, but I have to put in the same keyword three times in order to get the
hits, is there a way to type once and get the result?

SELECT Attorneys3.*, Attorneys3.PracticeArea
FROM Attorneys3
WHERE ((Attorneys3.PracticeArea) Like "*" & [Enter Search for Attorney3
Table] & "*");
UNION
SELECT Attorneys2.*,Attorneys2.PracticeArea
FROM Attorneys2
WHERE (((Attorneys2.PracticeArea) Like "*" & [Enter Search for Attorney2
Table] & "*"));
UNION
SELECT Attorneys1.*,Attorneys1.PracticeArea
FROM Attorneys1
WHERE (((Attorneys1.PracticeArea) Like "*" & [Enter Search for Attorney1
Table] & "*"));
Click to expand...
Click to expand...
 
J

John Spencer

Sorry, I don't understand what you are attempting to do. I would not attempt
to do this from a query.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
J

John Spencer

I forgot to comment on your three tables. If that union query is working then
your three tables are essentially the same. That means that you should have
ONE table with one (or maybe more) additional field that distinguishes between
the three entities (legal practice, partnership, firm?).



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Thanks John! That WORKED.

May I ask you one more question please.

I have seen someone can put a hypelink in one of fields for each record on
the result query table, click the link of the corresponding record, it opens
that record in the form view instead of table gridline view.

The reason for three tables because it is for three different entity, each
entity has it's own attorneys and practice area, as well as other different
fields.


John Spencer said:
Did you try the following? Note that all three prompts are identical and that
the trailing semicolons have been removed

SELECT Attorneys3.*, Attorneys3.PracticeArea
FROM Attorneys3
WHERE ((Attorneys3.PracticeArea) Like "*" &
[Enter Search for Attorney Table] & "*")
UNION
SELECT Attorneys2.*,Attorneys2.PracticeArea
FROM Attorneys2
WHERE (((Attorneys2.PracticeArea) Like "*" &
[Enter Search for Attorney Table] & "*"))
UNION
SELECT Attorneys1.*,Attorneys1.PracticeArea
FROM Attorneys1
WHERE (((Attorneys1.PracticeArea) Like "*" &
[Enter Search for Attorney Table] & "*"));


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi All,

I need to run any keyword search on the 3 tables, each table has one
specific field containing the information. For example, I type in "test" in
the search box/query, it will search all 3 fields in 3 different tables, and
provide me the hits if any. can you please help? thanks. I have the SQL
below, but I have to put in the same keyword three times in order to get the
hits, is there a way to type once and get the result?

SELECT Attorneys3.*, Attorneys3.PracticeArea
FROM Attorneys3
WHERE ((Attorneys3.PracticeArea) Like "*" & [Enter Search for Attorney3
Table] & "*");
UNION
SELECT Attorneys2.*,Attorneys2.PracticeArea
FROM Attorneys2
WHERE (((Attorneys2.PracticeArea) Like "*" & [Enter Search for Attorney2
Table] & "*"));
UNION
SELECT Attorneys1.*,Attorneys1.PracticeArea
FROM Attorneys1
WHERE (((Attorneys1.PracticeArea) Like "*" & [Enter Search for Attorney1
Table] & "*"));
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

Help creating a search form in Access XP 0
MS ACCESS: SEARCH in a split form 0
List all tables, including document structure 0
Keyword Search 4
Setting column width in a table with a macro is not working 0
Keyword Search works in 2003 but not 2007 ?? 1
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Multiple Keyword Search 3

Top