Link to home
Start Free TrialLog in
Avatar of Jeremy Campbell
Jeremy CampbellFlag for United States of America

asked on

Trying to optimize / speed up a delete query in Access.

I'm working on some append/delete queries that are moving sets of information from one table and putting it into an archive. The information it moves depends on the criteria from another query. I managed to get the append query set up and it runs really quick. Now I just need to delete the specifed data from my live table.

Here is my current delete query that runs WAY TOO slow;
DELETE tblFAICharacteristicNums.*, tblFAICharacteristicNums.JobNum
FROM tblFAICharacteristicNums
WHERE (((tblFAICharacteristicNums.JobNum) In (Select jpJobNum FROM [qryMoveCrit])));


Let me know if you have any ideas as to how I can speed this up.

Thank in advance!
Avatar of masteripper
masteripper

You should use a join instead of the IN Operator if u need speed.
Avatar of Jeremy Campbell

ASKER

Yeh, that's how I did the Append query but I got an error when I tried to do it in the delete query.. Here is the error:
User generated image
The SELECT DISTINCTROW should do the job
I'm not familiar with that.. Could you expand on how to accomplish that?
DELETE DistinctRow
Take a look also here
http://eis.bris.ac.uk/~ccmjs/access2000_distinct.htm
ASKER CERTIFIED SOLUTION
Avatar of masteripper
masteripper

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks folks! This got the job done!