Searching for data across multiple tables

[Eric]

How would one search for data entered across multiple
tables?

 

[John Vinson]

How would one search for data entered across multiple
tables?

Depends on the structure of the tables and the nature of the data, and
the criteria for the search.

In a properly designed relational database you would rarely need to do
this - do you perhaps have multiple tables of the same structure? If
so, consider consolidating all of the data into one table, maybe with
an extra field to designate which "group" of data it is (what is now
your table name). If not... please explain the situation!

 

[Eric]

The tables are similar, the common thread being a number
associated with all datatypes. The tables are similar in
design, but I would have to say they are not relational.
We want to do a search over multiple tables looking for
this common number and know which table it is associated
with.

I have attempted to link tables, and run queries, but to
no avail. I guess the only thing left to do is create one
giant table?

TIA

 

[John Vinson]

The tables are similar, the common thread being a number
associated with all datatypes. The tables are similar in
design, but I would have to say they are not relational.
We want to do a search over multiple tables looking for
this common number and know which table it is associated
with.

I have attempted to link tables, and run queries, but to
no avail. I guess the only thing left to do is create one
giant table?

If you want the search to be efficient, yes; note that in Access terms
I'd call 10,000,000 rows "giant" - are you in this range?

You do have an alternative though: a UNION query. Linking tables won't
work since as you say the tables aren't relational - nothing to link
on! A UNION query strings tables together "end to end". You need to go
into the SQL window to create one, but it's not hard:

SELECT thisfield, thatfield, anotherfield
FROM Table1
UNION ALL
SELECT thisfield, thatfield, someotherfield
FROM Table2
UNION ALL
<etc etc>

Each SELECT clause must use the same number of fields of matching
datatypes. Save the entire Query (as uniAllTables maybe) and then
create another query to search it. Don't expect blazing fast
performance...