Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jul 2003
    Thanked 0 Times in 0 Posts

    SQL Help (Access XP)

    I have a slow loading form that seems to be caused by a slow query... well the last of a series of queries, one building on the other. The first two query's open up nice and fast, the last takes ~30 sec. I am hoping to get some help speeding these up somehow. Any suggestions appreciated.
    Here they are: and thanks!

    SELECT [Job#], [ReviewDate], [NumberStations], [BlankLoadTableHeight], [ExitConveyorHeight], [MatlThickness], [ClampStroke], [LiftStroke], [Pitch], [PressTon], [Press], [RailDistance], [#SetsTooling], [PanelN], [PanelS], [PanelE], [PanelW], [PanelC], [BlankAcrossFlow], [BlankWithFlow]
    FROM [TblSubFCR]

    UNION SELECT [Job#], [ReviewDate], [NumberStations], [BlankLoadTableHeight], [ExitConveyorHeight], [MatlThickness], [ClampStroke], [LiftStroke], [Pitch], [PressTon], [Press], [RailDistance], [#SetsTooling], [PanelN], [PanelS], [PanelE], [PanelW], [PanelC], [BlankAcrossFlow], [BlankWithFlow]
    FROM [TblSubFIR]
    ORDER BY [Job#];

    UNION SELECT [Job#], [ReviewDate], [NumberStations], [BlankLoadTableHeight], [ExitConveyorHeight], [MatlThickness], [ClampStroke], [LiftStroke], [Pitch], [PressTon], [Press], [RailDistance], [#SetsTooling], [PanelN], [PanelS], [PanelE], [PanelW], [PanelC], [BlankAcrossFlow], [BlankWithFlow]
    FROM [TblSubHTR]
    ORDER BY [Job#];

    SELECT [Job#], ReviewDate, NumberStations, BlankLoadTableHeight, ExitConveyorHeight, MatlThickness, ClampStroke, LiftStroke, Pitch, PressTon, Press, RailDistance, [#SetsTooling], PanelN, PanelS, PanelE, PanelW, PanelC, ReviewDate, [BlankAcrossFlow], [BlankWithFlow]
    FROM QryReviewXX1 AS X
    WHERE (ReviewDate=(SELECT MAX(ReviewDate)
    From QryReviewXX1
    WHERE [Job#]=X.[Job#]));

    SELECT TblMasterSalesBol2.Archive, TblMasterSalesBol2.[Job#], QryReviewXX2.NumberStations, QryReviewXX2.BlankLoadTableHeight, QryReviewXX2.ExitConveyorHeight, QryReviewXX2.MatlThickness, QryReviewXX2.ClampStroke, QryReviewXX2.LiftStroke, QryReviewXX2.Pitch, QryReviewXX2.Press, QryReviewXX2.RailDistance, QryReviewXX2.[#SetsTooling], QryReviewXX2.PanelN, QryReviewXX2.PanelS, QryReviewXX2.PanelE, QryReviewXX2.PanelW, QryReviewXX2.PanelC, QryReviewXX2.ReviewDate, TblMasterSalesBol2.CustName, TblMasterSalesBol2.CustPlant, TblMasterSalesBol2.[CustPart#], QryReviewXX2.PressTon, QryReviewXX2.BlankAcrossFlow, QryReviewXX2.BlankWithFlow
    FROM QryReviewXX2 RIGHT JOIN TblMasterSalesBol2 ON QryReviewXX2.[Job#] = TblMasterSalesBol2.[Job#]
    WHERE (((TblMasterSalesBol2.Archive)<>1))
    ORDER BY TblMasterSalesBol2.[Job#];

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: SQL Help (Access XP)

    1. Remove the first ORDER BY clause in QryReviewXX1. You only need an ORDER BY at the end.
    2. Create a query qryMaxReviewDate with SQL

    SELECT [Job#], Max(ReviewDate) As MaxDate
    FROM QryReviewXX1
    ORDER BY [Job#]

    3. Change the SQL for QryReviewXX2 to

    SELECT QryReviewXX1.*
    FROM QryReviewXX1 INNER JOIN qryMaxReviewDate ON QryReviewXX1.[Job#] = qryMaxReviewDate.[Job#] AND QryReview1XX1.ReviewDate = qryMaxReviewDate.MaxDate

    If that doesn't help, it might be worth while to store the result of the first or second query in a temporary table, and use that as source for further queries.

Posting Permissions

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