"General" Search Across All Tables

[Guest]

I am at a bit of a loss and was hoping someone can point me in the right
direction. I am going to program a form that will allow a user to enter a
search term into a text field. Then when they press Search (or whatever...) I
want to be able to search through all the tables and find any incident of
that term regardless of what field it's in.

For example: User enters Smith

List returns:

John Smith - contact name - Contacts table
123 Smith Street (address) - Clients table
Smith and Company (plan name) - Plan table
Kevin Smith (company advisor) - Advisor table

Any ideas? I really don't know where to begin without making this extremely
complex.

Any help will be greatly appreciated.

 

[John Nurick]

On the rare occasions when I've had to do this I've ended up with a
humungous UNION query, e.g.

SELECT ContactID AS PK, ContactName AS TheField,
"Contacts" AS TheTable
FROM tblContacts
UNION
SELECT ContactID AS PK, Address AS TheField,
"Contacts" AS TheTable
FROM tblContacts
UNION
SELECT ClientID AS PK, Address AS TheField,
"Clients" AS TheTable
FROM tblClients
UNION
....
....
WHERE TheField LIKE "*SMITH*"
ORDER BY TheTable, TheField;