Querying ODBC Tables

[Patrick]

I have an Access 2003 Database which contains ODBC links to 3 external
(proprietary) databases of the same structure. i.e., all 3 external DBs
have a table called customer. They appear as "linked" tables db1_customer,
db2_customer, db_3customer

1) I want a query that list everything in 3 tables (i.e., if there are 3
records in db1_customer, 7 records in db2_customer and 11 records in
db3_customer), I want a SQL query that pulls back 21 records in total from
all 3 tables). Is this possible in SQL for access? If so, how? (I know I
could use VBA and loop through the 3 recordsets, but wondering if there's a
simply means).

2) Slightly more complicated here. I want to be able to "join" tables
together
joining the customer table with the order table in all 3 tables

 

[Patrick]

Resending, message sent by mistake before I finished..... sorry!!
----
I have an Access 2003 Database which contains ODBC links to 3 external
(proprietary) databases of the same structure. i.e., all 3 external DBs
have a table called customer. They appear as "linked" tables db1_customer,
db2_customer, db_3customer

1) I want a query that list everything in 3 tables (i.e., if there are 3
records in db1_customer, 7 records in db2_customer and 11 records in
db3_customer), I want a SQL query that pulls back 21 records in total from
all 3 tables). Is this possible in SQL for access? If so, how? (I know I
could use VBA and loop through the 3 recordsets, but wondering if there's a
simply means).

2) Slightly more complicated here. I want to be able to "join" tables
together
joining the customer table with the order table in all 3 databases
i.e.
(db1_customer x db1_order), (db2_customer x db2_order),(db3_customer x
db3_order)
(i.e., the dataset containing the cross join of customer table and order
table from all 3 databases). Is this at all possible? If so, how?

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

1) Use a UNION query, like this:

SELECT * FROM db1_customer
UNION ALL
SELECT * FROM db2_customer
UNION ALL
SELECT * FROM db3_customer

The column order must be the same for all tables. If the order is
different in each table put the column names, in the same order, in each
SELECT clause.

2) Not quite sure what you want in this Q. So...use the UNION set up &
JOINs to join the tables. The separate SELECT statements would be
better done using a pass-thru query and then unioning the results
together.

Access query:

SELECT c.id, o.order
FROM db1_customer c inner join db1_orders o
on c.id = o.id
WHERE <criteria as needed>

UNION ALL

SELECT c.id, o.order
FROM db2_customer c inner join db2_orders o
on c.id = o.id
WHERE <criteria as needed>

UNION ALL

SELECT c.id, o.order
FROM db3_customer c inner join db3_orders o
on c.id = o.id
WHERE <criteria as needed>

Using pass-thru qrys (use the separate qrys above in pass-thru qrys and
save them in Access w/ the indicated names):

SELECT * FROM qryOrdersDB1
UNION ALL
SELECT * FROM qryOrdersDB2
UNION ALL
SELECT * FROM qryOrdersDB3

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQT9WB4echKqOuFEgEQK/3wCfcaM0iWT4YwyPB6jnENgfTvo47l4AnA0C
3UqNYTKllloXaRcIhnHP7+jX
=8PWS
-----END PGP SIGNATURE-----