Join

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

I would like to be able to join several tables in a query
so I can search more than one database at a time. Can
someone point me in the right direction with instruction?

Thank you

Peter
 
Sure, say we have two tables, table_1 and table_2. Each table has several columns, but one in common (cust_no)

Your SQL make look something like this

SELECT t1.*, t2.*
FROM table_1 t1
JOIN table_2 t2 ON t1.cust_no = t2.cust_n

Hope it helps
To
 
-----Original Message-----
Sure, say we have two tables, table_1 and table_2. Each
Click to expand...
table has several columns, but one in common (cust_no).
Your SQL make look something like this:

SELECT t1.*, t2.*
FROM table_1 t1
JOIN table_2 t2 ON t1.cust_no = t2.cust_no

Hope it helps,
Tom

.
Thanks Tom.
Click to expand...

I'm kind of new to this and need more specific
instructions. Say I have two or more tables with a field
calls Last Name. I would like to be able to connect all of
those tables together, query all of them at the same time
and be able to find a last name of for example Gunther. I
would like to be able to view his full name and address. I
have many databases and it's very time consuming to query
each database seperatly.

Thanks

Peter
 
I'm kind of new to this and need more specific
instructions. Say I have two or more tables with a field
calls Last Name. I would like to be able to connect all of
those tables together, query all of them at the same time
and be able to find a last name of for example Gunther. I
would like to be able to view his full name and address. I
have many databases and it's very time consuming to query
each database seperatly.
Click to expand...

I think I asked this yesterday but I'll ask again:

Do you have many *DATABASES* - multiple .mdb files, each of which can
contain many tables, queries, forms, and reports?

Or do you have many *TABLES* within a Database?

You can do this either way, but it's a rather different technique!

If you have multiple Tables in a database, you can use a UNION query
to splice them all together. See the online help for UNION.

But if you do - you're not structuring your database correctly. If you
have multiple identical tables, consider using Append queries to
create one big table, perhaps with another field to distinguish what
are now separate tables.
 
I have multiple databases. They all have tables and
queries and forms. I would like to link them up to one
query that would find specific info. from a database.

Thanks

Pete
 
I have multiple databases. They all have tables and
queries and forms. I would like to link them up to one
query that would find specific info. from a database.
Click to expand...

You'll need to select one of the databases, or create a new database,
and use File... Get External Data... Link to link to the tables in the
multiple databases, and then create a UNION query linking them all.
This query will be GLACIALLY slow since it must open each database and
incur the overhead of the linked search.

I'll be out of town for a couple of weeks so I won't be able to
reply... but I'd still strongly suggest that you think carefully about
the disadvantages of storing data (categories?) in database names!
Databases aren't really "documents" like a Word document or an Excel
spreadsheet; a Database is an environment, and typically if you have
multiple sets of related data (as you evidently do) you should store
them all in one database; if they are multiple sets of the *same kind
of data*, they should all be stored in one Table.
 
I have several independent data bases or mdb files. Each
one has a table and a few queries. Some have a report
also. As an example if I have a Colorado.mdb and a
Nebraska.mdb databases which are seperate from each other.
They both have basically the same fields in their own
tables. Lets say I want to find the last name Gunther but
I don't know if it's in the Colorado or Nebraska
databases. Is there a way to hook up both databases to
query both of them for the name Gunther without having to
first open the Colorado database, search for Gunther and
than the Nebraska database. I have several databases like
this that I would like to be able to tie together so to
speak and query all of them at the same time for specific
information in a specific field.

Peter
 
I have several independent data bases or mdb files. Each
one has a table and a few queries. Some have a report
also. As an example if I have a Colorado.mdb and a
Nebraska.mdb databases which are seperate from each other.
Click to expand...

Please reread my previous message. You are storing the same kind of
data in different databases, and storing DATA - the name of a state -
in the name of the .mdb file. THIS IS BAD DESIGN and it will make your
job harder. If you like your job to be hard, that's fine... but if you
had all this data *in one table*, with an indexed State field, you
could solve your problem very easily; and you could still have
separate state-specific queries.
They both have basically the same fields in their own
tables. Lets say I want to find the last name Gunther but
I don't know if it's in the Colorado or Nebraska
databases. Is there a way to hook up both databases to
query both of them for the name Gunther without having to
first open the Colorado database, search for Gunther and
than the Nebraska database.
Click to expand...

Yes. And I explained exactly how to do it in the previous post. Here
you have it again.

Create a new empty database, AllStates let's say.

In that database use File... Get External Data... Link to link to the
tables in each state specific database (giving you all the tables in
one database).

Then open the online Help and search for "UNION". Follow the
instructions to create a UNION query, which will string together the
Nebraska data and the Colorado data and the Idaho data and so on into
one big pseudo-table, which can be searched in a single pass.
 
Back
Top