I have an Access application with SQL Server. It has a query that cannot run in Access when the database is large. However, the query runs well in SQL Query Analyzer. Could someone give me advice, please?

SELECT TRANSPORTER.AS_ID AS AS_ID, TRANSPORTER.CTRY_ID AS CTRY_ID, Count(TRANSPORTER.T_ID) AS Count
FROM TRANSPORTER WHERE
(TRANSPORTER.T_STATUS=0
AND (TRANSPORTER.T_ID Not In
(SELECT WITHDRAWAL.T_ID FROM WITHDRAWAL WHERE
(IsNull(WITHDRAWAL.W_ENDDATE)<>False and WITHDRAWAL.W_STARTDATE<=Date())
)
)
)
OR
(TRANSPORTER.T_STATUS=1 AND TRANSPORTER.T_EOADATE>Date())
GROUP BY TRANSPORTER.AS_ID, TRANSPORTER.CTRY_ID

Maybe you can try using NOT EXISTS to reduce table scans:

Code:

SELECT TRANSPORTER.AS_ID AS AS_ID, TRANSPORTER.CTRY_ID AS CTRY_ID, Count(TRANSPORTER.T_ID) AS Count
  FROM TRANSPORTER
 WHERE (TRANSPORTER.T_STATUS=0 
   AND NOT EXISTS (
    SELECT 1 FROM WITHDRAWAL
     WHERE TRANSPORTER.T_ID = WITHDRAWAL.T_ID 
       AND IsNull(WITHDRAWAL.W_ENDDATE)<>False AND WITHDRAWAL.W_STARTDATE<=Date()))
    OR (TRANSPORTER.T_STATUS=1
   AND TRANSPORTER.T_EOADATE>Date())
 GROUP BY TRANSPORTER.AS_ID, TRANSPORTER.CTRY_ID

Replace IsNull(WITHDRAWAL.W_ENDDATE)<>False by WITHDRAWAL.W_ENDDATE Is Null. Thank you.