Query question

  • Thread starter Thread starter Bill Gates
  • Start date Start date
B

Bill Gates

Hello,

Question:
Can you create a query linking tables that are in
different databases?

Example:
I have three databases entitled January, February, and
March. Each of these databases contains a table
called "Customer Sales." If I want to compare monthly
sales (Jan vs Feb vs Mar) of Customer A within a fourth
database called "Analysis," can I create a query in
the "Analysis" database linking the three Customer Sales
tables in the three monthly databases? Or, do I need to
copy all three "Customer Sales" tables (one in each
monthly database) into my Analysis database in order to
create my monthly comparison query? If I can query tables
within different databases, how do I do this? When I try
to select tables for my queries, I'm only seeing queries
in my open database.

Thanks in advance for your help.

-Bill
 
Hi, we have an Access database that has linked tables which point to
tables in other databases and we run reports off those tables fine. Not
sure if you have to create the links to the other tables to access them
but that's how we do it.

Bill Gates wrote:
 
Hello,

Question:
Can you create a query linking tables that are in
different databases?

Example:
I have three databases entitled January, February, and
March.
Click to expand...

This is EXTREMELY bad design, I fear. Storing data in a tablename is
unwise; storing data in a database name is even worse. Do you have
more than 5-10 million records in each of these databases? If not, I'd
strongly suggest having *one* table in a single database, with a date
or a month field.
Each of these databases contains a table
called "Customer Sales." If I want to compare monthly
sales (Jan vs Feb vs Mar) of Customer A within a fourth
database called "Analysis," can I create a query in
the "Analysis" database linking the three Customer Sales
tables in the three monthly databases?
Click to expand...

Yes; you can use File... Get External Data... Link to connect your
Analysis database to each of these tables; you can create queries
joining the tables.
Or, do I need to
copy all three "Customer Sales" tables (one in each
monthly database) into my Analysis database in order to
create my monthly comparison query?
Click to expand...

You can do that too - use File... Get External Data... Import instead
of Link. This might give better performance on your queries. Note that
it will also give you two copies of each table, so changes made in
February.mdb will not be reflected in Analysis.mdb unless you empty
the table and re-import.
If I can query tables
within different databases, how do I do this? When I try
to select tables for my queries, I'm only seeing queries
in my open database.
Click to expand...

If you Link, you'll see the remote table in the Tables window (with an
icon indicating that it's linked) and can build queries with it just
as if it were local.
 
Back
Top