Select Query across 2 databases

[EPD Hater]

I am querying a table in one table/database and using another table in another database to limit my criteria. When I run this without the subquery, it's fine; however when I run this the way it's shown below, it seems to want to take forever.

SELECT [clear void date]
FROM [tblCleared Comp2]
 IN "C:\WINNT\PROFILES\NTSILAKI\DESKTOP\ESCHEATMENT STUFF\AUTODATA.MDB"
WHERE date between #7/1/03# and #7/31/03#
 and [RECON CODE] = "21"
 and [bank memo] in
(SELECT distinct CheckNumber
 FROM qry_AlreadyCleared_Borr_21
  IN "C:\WINNT\PROFILES\NTSILAKI\DESKTOP\ESCHEATMENT STUFF\unclaimed_NT.mdb");

The results of the parent query without the subquery is 1,434 rows (and the qry_AlreadyCleared_Borr_21 resolves to 10 rows) so I should have only 10 rows in the end. What is wrong here? Thanks...

 

[EPD Hater]

Instead of deleting this thread, I'll offer my own solution for archival and search purposes. The subquery should be run against a table, not a query! It speeds up the process!

 

[EPD Hater]

If you use a join rather than a subquery, that should be faster still.

I can join two tables from different databases? I had trouble doing that so that's why i used the subquery. I'll have to try it again. Thanks!

 

[EPD Hater]

The data in the other database table is always being changed/updated. If I link that table as opposed to accessing it through a query only, how will that benefit in processing if it still needs to obtain a current "snapshot" of the data inside?

 

[dcx693]

EPD Hater, the link is a "live link" meaning that it takes the current data from the other db, it doesn't import a copy of it. You don't just link the table to your current db, you link it then add it to your query.