Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Posts
    1

    Unanswered: query taking forever to execute!

    SELECT FCST.REF_DT AS [Date Forecasted], ACTUAL.REF_DT AS [Date Shipped], FCST.QT AS [Qt Forecasted], ACTUAL.QT AS [Qt Shipped], FCST.PART_NR, FCST.REGION, FCST.TYPE, IIf(FCST.QT=0,'',formatpercent((ACTUAL.QT-FCST.QT)/FCST.QT,2)) AS MPE

    FROM TBL_FCST_WKLY AS FCST, TBL_ACTUAL_WKLY AS ACTUAL

    WHERE (FCST.TYPE=ACTUAL.TYPE) AND (FCST.REGION=ACTUAL.REGION) AND (FCST.PART_NR=ACTUAL.PART_NR) AND

    FCST.REF_DT = (SELECT DISTINCT DATEADD("ww",-LT.LEADTIME,ACTUAL.REF_DT) AS FORECASTED FROM TBL_FCST_WKLY FCST2 INNER JOIN LEADTIME LT ON LT.COMPONENT=FCST2.PART_NR AND LT.REGION=FCST2.REGION WHERE FCST2.PART_NR=FCST.PART_NR AND FCST2.REGION=FCST.REGION) AND

    FCST.MONTH_DT = (SELECT DISTINCT DATEADD("ww",LT.LEADTIME,FCST.REF_DT) AS FORECASTED FROM TBL_FCST_WKLY FCST2 INNER JOIN LEADTIME LT ON LT.COMPONENT=FCST2.PART_NR AND LT.REGION=FCST2.REGION WHERE FCST2.PART_NR=FCST.PART_NR AND FCST2.REGION=FCST.REGION);


    i am querying two tables TBL_FCST_WKLY which has 29500 records and TBL_ACTUAL_WKLY which has 798222 records.
    When i run this query it sites there forever. Is it because i dint optimize my query or is it because the number of records is too much for access? can someone help plz.

    thanks

  2. #2
    Join Date
    Jul 2005
    Posts
    39
    Is FCST2 a separate table or a replica of FCST? If TBL_ACTUAL_WKLY is a complete history of transactions, do you think using a query subset (of it to match the date range of the TBL_FCST_WKLY) instead of the whole table would help?

  3. #3
    Join Date
    Feb 2004
    Posts
    137
    The answer is:

    SELECT FCST.REF_DT AS [Date Forecasted], ACTUAL.REF_DT AS [Date Shipped], FCST.QT AS [Qt Forecasted], ACTUAL.QT AS [Qt Shipped], FCST.PART_NR, FCST.REGION, FCST.TYPE, IIf(FCST.QT=0,'',formatpercent((ACTUAL.QT-FCST.QT)/FCST.QT,2)) AS MPE

    FROM (TBL_FCST_WKLY AS FCST INNER JOIN TBL_ACTUAL_WKLY AS ACTUAL ON FCST.TYPE=ACTUAL.TYPE AND FCST.REGION=ACTUAL.REGION AND FCST.PART_NR=ACTUAL.PART_NR)
    INNER JOIN LEADTIME LT ON FCST.PART_NR=LT.COMPONENT AND FCST.REGION=LT.REGION

    WHERE FCST.REF_DT=DATEADD("ww",-LT.LEADTIME,ACTUAL.REF_DT)
    AND FCST.MONTH_DT=DATEADD("ww",LT.LEADTIME,FCST.REF_DT );

Posting Permissions

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