Criteria for generating reports

[Guest]

Hi,
I have been able to set up a query and associated report that will print
only for the person specified by user.
However, is it possible that a search can be made across several non-related
tables for that same name?
All tables have ID Number and Name in common, but that's it.
I want to be able to search all tables so that I don't have to remember
which table a particular person is stored in.
Thanks.

 

[SA]

Cathy:

If the data structures of the various tables are the same, then the best
solution would be to modify your reports underlying query and do a union
between the various tables. To do this, you'd create a new query and then
from the query menu item, choose SQL specific type of "union query".

A union query is then constructed by adding SQL to the blank SQL window. It
normally constructed like this:

Select Field1, Field2, Field3 from SomeTable
Union Select Field1, Field2, Field3 from SomeOtherTable
Union Select Field1, Field2, Field3 from YetAnotherTable

Now with that said, one has to ask why these individual records are stored
in different tables, or why if there is different data between tables, there
is not a master table with all persons in it; then the original tables may
have referential links to the new main persons table.