Query to search in multiple tables

[Brigham Siton]

I would want to create a query that will search a field in multiple tables.

I have 4 tables that contains dentists. These dentists have their tax-id
numbers. However, one can be on 4 different tables.

How will I search the tax-id number field on all 4 tables and show the
address, city, state, zip & phone in the result.

Thank you very much in advance.

 

[Ken Snell]

Use a UNION query as the query that you do the searching with. You can have
all four tables show up in one query this way, and you then can search all
the tables at once.

Your question suggests that your table structure is not normalized. Any
particular reason you could not put all the dentists in one table and use a
field to show what is different about them?

 

[Brigham Siton]

The data is coming in form 4 different sources. These sources represents
different groups that the dentists is a part of, sort of like insurance
company programs.

These tables are coming in literally from different insurance companies that
we have to load into our system.

However, you are right about normalizing the tables. We would want to
normalize the tables eventually.

Thank you very much for your help.

 

[Tom Ellison]

Dear Brigham:

In most cases, it is good to just put all the dentists into one table
and have some field that relates to which of the 4 sets of dentists
you have.

A UNION query will probably give you a way of looking at the 4 tables
in line with what you want.

SELECT TaxId, City, State, Zip, Phone, 1 AS Source
FROM Dentist1
UNION ALL
SELECT TaxId, City, State, Zip, Phone, 2 AS Source
FROM Dentist2
UNION ALL
SELECT TaxId, City, State, Zip, Phone, 3 AS Source
FROM Dentist3
UNION ALL
SELECT TaxId, City, State, Zip, Phone, 4 AS Source
FROM Dentist4

You must use your actual column names and table names, of course.

I would want to create a query that will search a field in multiple tables.

I have 4 tables that contains dentists. These dentists have their tax-id
numbers. However, one can be on 4 different tables.

How will I search the tax-id number field on all 4 tables and show the
address, city, state, zip & phone in the result.

Thank you very much in advance.

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts