Does this subquery rerun for every row?

[VBAjedi]

I'm writing a query to compare two copies of a table for changes (each copy contains about 10,000 records). The unique key that I'm matching on is a combination of two fields. So...

I wrote a query for each table that simply selects all fields from the table and adds a concatenated field to create the unique key. Then I used those two queries as my FROM sources (which is essentially the same thing as a subquery, right?).

It works but it's running extremely slow, making me wonder if it's rerunning those two queries for each of the 10,000 rows?!?! I wouldn't think it needs to, but...

Here's the actual query. "CurrCReportMOD" and "PastCReportMod" are the two queries I use to create the concatenated field, which is "POSNOandEIN":

SELECT 
CurrCReportMOD.POSNOandEIN, 
PastCReportMOD.POSNOandEIN, 
CurrCReportMOD.EIN, 
CurrCReportMOD.[Employee Name],  
'New Employee placement' AS Status, 
CurrCReportMOD.Posno AS PositionNo 
FROM 
CurrCReportMOD LEFT JOIN 
PastCReportMOD ON CurrCReportMOD.POSNOandEIN= PastCReportMOD.POSNOandEIN
WHERE 
PastCReportMOD.EIN Is Null And 
CurrCReportMOD.EIN Is Not Null

VBAjedi

 

[VBAjedi]

Oops... meant to post this to the Access forum...

VBAjedi

 

[PHV]

What is the SQL code of CurrCReportMOD and PastCReportMOD ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886