Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query won't run (2003 SP2)

    I am in the middle of a minor Db update. But the Db, which I have been using for ages, suddenly stops at executing a query. The query starts, but bogs down and freezes. Pressing Esc brings it out of its coma. But the query hasn
    Regards,
    Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query won't run (2003 SP2)

    Not In (...) is notoriously slow. You could create a temporary table listing the values of Sorter from qryLetterTblWithEmail. You can then use an outer join with the temp table instead of Not In (...)

    Note: a memo field is not the solution - if anything, it'll slow down things even further.

    I find it rather worrying that performance varies so widely, and that Ctrl+C and right-click don't work much of the time. This would seem to indicate a serious problem with your PC. Have you looked at the Processes tab of Task Manager to see if something is gobbling up processor time or resources?

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query won't run (2003 SP2)

    Thanks Hans

    Not wanting to introduce another Temp table, I tried the following

    INSERT INTO tblTempStore2 ( Sorter )
    SELECT qryLetterTbl.*, qryLetterTblWithEmail.Sorter
    FROM qryLetterTbl LEFT JOIN qryLetterTblWithEmail ON qryLetterTbl.Sorter = qryLetterTblWithEmail.Sorter
    WHERE (((qryLetterTblWithEmail.Sorter) Is Null));

    But this gives a dual output of [Sorter]. Can you see any way of not outputting the qryLetterTblWithEmail.Sorter?

    The performance doesn
    Regards,
    Peter

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query won't run (2003 SP2)

    Doesn't the following do that:

    INSERT INTO tblTempStore2 ( Sorter )
    SELECT qryLetterTbl.*
    FROM qryLetterTbl LEFT JOIN qryLetterTblWithEmail ON qryLetterTbl.Sorter = qryLetterTblWithEmail.Sorter
    WHERE qryLetterTblWithEmail.Sorter Is Null;

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    637
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query won't run (2003 SP2)

    That results in error message "Number of query values and destination fields are not the same."
    So I altered the line INSERT INTO tblTempStore2 ( Sorter ) to INSERT INTO tblTempStore2
    Worked a treat.
    Thanks heaps for your input.
    Your approach to all problems is methodical, rigorous and very, very knowledgeable. You boggle my mind. Thanks again.

    I will start another thread re the 'Copy' problem.
    Regards,
    Peter

Posting Permissions

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