Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

Trying to setup a delete query in access based on results of another query.

Here is my Delete query as of right now.
dltquery
I want to delete every row in the tblJobLog where tblJobLog.JobNum = qryJobLogDelCrit.jhJobNum.

Here is the sql for my current delete query;
DELETE tblJobLog.*
FROM tblJobLog INNER JOIN qryJobLogDelCrit ON tblJobLog.JobNum = qryJobLogDelCrit.jhJobNum


When I run this query as a Select query it will give me a list of JobNums that I would like to delete. I thought If I just changed it to a Delete query then it would delete those rows from tblJobLog.

When I try to run it I get the following error message;
Error
Thanks for the help!
0
Jarred Meyer
Asked:
Jarred Meyer
  • 3
  • 3
2 Solutions
 
mbizupCommented:
Try this:

DELETE *
FROM tblJobLog
WHERE JobNum IN (SELECT jhJobNum FROM qryJobLogDelCrit)



It may also work without the *:

DELETE
FROM tblJobLog
WHERE JobNum IN (SELECT jhJobNum FROM qryJobLogDelCrit)
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Is that rerunning qryJobLogDelCrit for every record in tblJobLog? Seems like that may be the case because it just hangs.. Is there a quicker method by chance?
0
 
mbizupCommented:
What is the SQL for qryJobLogDelCrit?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Rey Obrero (Capricorn1)Commented:
<Is there a quicker method by chance?>

create a table from the result of running qryJobLogDelCrit, name it tmpTable
( or change the qryJobLogDelCrit into a make table query)

then run this delete query

DELETE *
FROM tblJobLog
WHERE JobNum IN (SELECT jhJobNum FROM tmpTable)
0
 
Jarred MeyerProduction ManagerAuthor Commented:
mbixup.. I'm sorry, I forgot to get all of my extra tables out of that query. qryJobLogDelCrit used to have a bunch of extra tables and calculations in it (I copied it from another table that had a bunch of stuff in it)

Once I deleted all the extra stuff from it, it ran almost immediately.

Thanks for the help there!

Thanks also Capricorn for your offering as well.. It would have solved the problem as well.
0
 
mbizupCommented:
It sounds like you're set, but you probably could also work the criteria of qryJobLogDelCrit right into your delete query, to reduce it to a single query.  

That would be my preferred approach if the second query was not overly complex, and was where I was heading asking about the SQL
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Gotcha. Thanks again!
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now