Creating a multiple table query...

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

Guest

I have a database that has multiple tables and would like to do a few thing.
First off all of the tables have the same colums and layout. What I would
like to do is create a query that get a user input, looks at the data in all
of the table and give any rows that match that string. So I need help with
to thing, makeing the query look at multiple tables and also making it so
that the user will only have to type in the item they want searched and it
will look at all of the colums in the table to see if it matches the string.
If anyone could help that would great.
 
Caution: Serious Hack Ahead...

If you UNION all of the tables together in one query, you can apply criteria
to return the needed records from all.

Select * from T1
UNION
Select * from T2
UNION
Select * from T3
UNION
Select * from T4
WHERE ...
 
I have a database that has multiple tables and would like to do a few thing.
First off all of the tables have the same colums and layout.
Click to expand...

The first thing you should do is... Redesign your badly flawed
database.

You're "storing data in tablenames". A MUCH better design would be
*ONE* big table containing all the data in these tables, with one
additional field indicating which table the data came from.
What I would
like to do is create a query that get a user input, looks at the data in all
of the table and give any rows that match that string. So I need help with
to thing, makeing the query look at multiple tables and also making it so
that the user will only have to type in the item they want searched and it
will look at all of the colums in the table to see if it matches the string.
Click to expand...

This is usually a bad idea too. Typically each field in a table
contains a particular type of data. You wouldn't want to search a
Zipcode field for last names, or a LastName field for phone numbers. I
don't know what's in your tables, but I've never seen a *good* example
of a table where it's appropriate to blindly search all fields in a
table.
If anyone could help that would great.
Click to expand...

Your first step is to normalize your data structure. Could you
describe what information you're storing, and how these tables are
structured?

The UNION query will solve your stated problem... but that's not your
*real* problem!

John W. Vinson[MVP]
 
Back
Top