Search in multiple databases/tables

[Nancy]

I have three databases with the same structure, table and field names (data
tables are not linked, but can be), I need to be able to search all three
databases for document number. THe user would like a drop down of all
documents from all 3 databases (3 separate tables) Do I create a separate
database linking to all three? Can I get all documents from 3 tables into one
query or what is the best way to set this up?

Thank You

nancye

 

[Mark]

Hi Nancy,

Why do you have document data in three separate databases.? Perhaps you need
to revise the structure and put all the document data in one table.
Searching for a document number would then be very simple. To do this, you
may need a new document type table. If you respond back with the document
table structure in each database, I may be able to help you further.

Steve

 

[John... Visio MVP]

Careful Nancy, Steve is a known for conning people into free help and then
charging them. There are far better qualified people here who will honour
the purpose of these newsgroups as a place for FREE help.

John...

 

[John W. Vinson]

Please go get lost Steve...

To the original poster:
Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

In Steve's defense, he (uncharacteristically) didn't solicit for work in this
particular post, but rather gave some accurate advice. I know why you're
packing this sixgun, but maybe your hairtrigger is a bit too sensitive.

 

[John W. Vinson]

I have three databases with the same structure, table and field names

Why? That's a very badly flawed design. You're storing data in tablenames or
(even worse) database names; you would be much better off with *one table*,
perhaps with another field indicating which collection of data this record is
from.

(data
tables are not linked, but can be), I need to be able to search all three
databases for document number. THe user would like a drop down of all
documents from all 3 databases (3 separate tables) Do I create a separate
database linking to all three? Can I get all documents from 3 tables into one
query or what is the best way to set this up?

You could use a fourth database (a "frontend") with no tables, but just links
to these three, and a UNION query stitching them all together. Performance
will likely be abominably bad but it *could* be done.