Seach multiple tables at once

  • Thread starter Thread starter HekaEnt via AccessMonster.com
  • Start date Start date
H

HekaEnt via AccessMonster.com

I have 3 different tables(each one for a different electonic document)
containing similar information reguarding part number, customer, creator and
dates etc. and what i would like to do is create a blank form called [search]!
and have [search]! contain a text box [txtBox] and make it possible so that
when i click on the one button on the form that it will search all 3 tables
with the infomartion entered in the text box and bring up the corresponding
information. i read something about a query that could do something similar
to this, but basically i want a search function that searches through all
columns of the enlisted 3 tables. if this is not possible then please tell me
as this is a "would like to have" by the owners of the company.
 
IF the three tables are parallel in structure you could use a union query to
combine them into one and search that for the results. The SQL of that
might look something like the following

SELECT Part, Customer, Creater
FROM TableOne
WHERE Part = [Forms]![Search]![txtBox]
UNION
SELECT PartNum, CustomerName, CreatedBy
FROM TableTwo
WHERE Part = [Forms]![Search]![txtBox]
UNION
SELECT Part, Customer, Manufacturer
FROM TableThree
WHERE Part = [Forms]![Search]![txtBox]

Read the help on UNION queries to understand the rules and limitations on
this type of query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top