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!
LVL 2
Jarred MeyerProduction ManagerAsked:
Who is Participating?
 
masteripperCommented:
Just make the Query in the Editor and then switch to SQL and after DELETE use DISTINCTROW
e.g "DELETE tblFAICharacteristicNums.*, tblFAICharacteristicNums.JobNum
FROM tblFAICharacteristicNums INNER JOIN ...."

then
"DELETE DISTINCTROW tblFAICharacteristicNums.*, tblFAICharacteristicNums.JobNum
FROM tblFAICharacteristicNums INNER JOIN ....."
0
 
masteripperCommented:
You should use a join instead of the IN Operator if u need speed.
0
 
Jarred MeyerProduction ManagerAuthor Commented:
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:
error
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
masteripperCommented:
The SELECT DISTINCTROW should do the job
0
 
Jarred MeyerProduction ManagerAuthor Commented:
I'm not familiar with that.. Could you expand on how to accomplish that?
0
 
masteripperCommented:
DELETE DistinctRow
Take a look also here
http://eis.bris.ac.uk/~ccmjs/access2000_distinct.htm
0
 
masteripperCommented:
0
 
Rey Obrero (Capricorn1)Commented:
using the query "qryMoveCrit" might be the one slowinf down your delete query.

since you already have added to the archived table the records you want to delete,

try using this

DELETE tblFAICharacteristicNums.*, tblFAICharacteristicNums.JobNum
FROM tblFAICharacteristicNums
WHERE (((tblFAICharacteristicNums.JobNum) In (Select jpJobNum FROM [ARCHIVEDTABLENAME])))

post back the result..
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Thanks folks! This got the job done!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.