Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    9

    Unanswered: query runs in Query Analyzer but not in Access (was "Help my query, please")

    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

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    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


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Feb 2005
    Posts
    9
    Replace IsNull(WITHDRAWAL.W_ENDDATE)<>False by WITHDRAWAL.W_ENDDATE Is Null. Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •