Delete records based on query

Access need help understanding why this query will work as a SELECT Query but when I change it to a DELETE Query it keeps asking for me to specific the table containing the records I want to delete.
I want to delete the records in MyTable based on the records in Query2.

DELETE MyTable.TAG_Number, *
FROM MyTable INNER JOIN Query2 ON MyTable.ID = Query2.ID
WHERE (((MyTable.TAG_Number)<>"False"));
ssblueCoordinatorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

irudykCommented:
This should read:

DELETE MyTable.*
FROM MyTable INNER JOIN Query2 ON MyTable.ID = Query2.ID
WHERE (((MyTable.TAG_Number)<>"False"));
ssblueCoordinatorAuthor Commented:
Still getting error "Could not delete from specified tables ?????
irudykCommented:
Maybe it's got something to do with Query2. If you run the SELECT and you are not able to edit the contents from the results that appear, then Query 2 is what is preventing the deletion.

Try using the following:

DELETE MyTable.*
FROM MyTable
WHERE MyTable.TAG_Number<>"False"
AND MyTable.ID IN (SELECT Query2.ID FROM Query2)
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

ssblueCoordinatorAuthor Commented:
The query seems to be running but it is going very, very, very slow??
irudykCommented:
What is the SQL syntax for Query2?
ssblueCoordinatorAuthor Commented:
SELECT *
FROM MyTable
WHERE (((MyTable.Tag_Number) In (SELECT DISTINCT [Tag_Number] FROM [MyTable] WHERE [New_Interval] <1001)));
irudykCommented:
Does
SELECT MyTable.TAG_Number, *
FROM MyTable
WHERE MyTable.TAG_Number<>"False"
AND MyTable.[New_Interval] <1001

Open in new window

give the same results as
SELECT MyTable.TAG_Number, *
FROM MyTable INNER JOIN Query2 ON MyTable.ID = Query2.ID
WHERE MyTable.TAG_Number<>"False"

Open in new window

If so, use:
DELETE MyTable.*
FROM MyTable
WHERE MyTable.TAG_Number<>"False"
AND MyTable.[New_Interval] <1001

Open in new window

John TsioumprisSoftware & Systems EngineerCommented:
DELETE *
FROM MyTable
WHERE MyTable.TAG_Number<>"False"
AND  MyTable.ID IN (SELECT  Query2.ID FROM Query)

Open in new window

ssblueCoordinatorAuthor Commented:
The problem with all the suggestions is that only the records with an New_Interval  <1001 are being deleted.
The query is pulling all the records but I can't seem to delete all the records.
 I need to delete all records associated with the same  TAG_Number.

explanation:  TAG_Number 123 has 6 records only one has an New_Interval  <1001   -   I need to delete all 6 records.

The more complicated queries never seem to run, they start but never finish - I let one run over night and it still didn't finish.
irudykCommented:
Okay, so wouldn't this work

DELETE MyTable.*
FROM MyTable
WHERE (((MyTable.Tag_Number) In (SELECT DISTINCT [Tag_Number] FROM [MyTable] WHERE [New_Interval] <1001)));

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark EdwardsChief Technology OfficerCommented:
First of all, you can't delete records from an Access table using a query where the table is joined on another table or query.  The "In" criteria approach needs to be used if pure criteria in the records themselves cannot be used (i.e. new_interval<1001).  Also, how many records and are the criteria fields indexed?  (just checking)

Depending on the number of records in your Access table, it may take awhile, but the SQL in irudyk's post above should work.
ssblueCoordinatorAuthor Commented:
It seems like it is working but it never finishes!
ssblueCoordinatorAuthor Commented:
It finally ran!! : )  Thanks for the help and patience and information.
Mark EdwardsChief Technology OfficerCommented:
who's buying lunch.....
Mark EdwardsChief Technology OfficerCommented:
p.s.  In SQL Server, you CAN delete records in a table based on a link to another table, but you can't do it in Access.
Frustrating to those who are use to SQL Server.
ssblueCoordinatorAuthor Commented:
Stop by and I will!!  : )  Thanks everyone!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.