Delete records based on query

ssblue
ssblue used Ask the Experts™
on
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"));
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
This should read:

DELETE MyTable.*
FROM MyTable INNER JOIN Query2 ON MyTable.ID = Query2.ID
WHERE (((MyTable.TAG_Number)<>"False"));
ssblueCoordinator

Author

Commented:
Still getting error "Could not delete from specified tables ?????
Top Expert 2008

Commented:
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)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ssblueCoordinator

Author

Commented:
The query seems to be running but it is going very, very, very slow??
Top Expert 2008

Commented:
What is the SQL syntax for Query2?
ssblueCoordinator

Author

Commented:
SELECT *
FROM MyTable
WHERE (((MyTable.Tag_Number) In (SELECT DISTINCT [Tag_Number] FROM [MyTable] WHERE [New_Interval] <1001)));
Top Expert 2008

Commented:
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 Engineer

Commented:
DELETE *
FROM MyTable
WHERE MyTable.TAG_Number<>"False"
AND  MyTable.ID IN (SELECT  Query2.ID FROM Query)

Open in new window

ssblueCoordinator

Author

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.
Top Expert 2008
Commented:
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)));
Mark EdwardsChief Technology Officer

Commented:
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.
ssblueCoordinator

Author

Commented:
It seems like it is working but it never finishes!
ssblueCoordinator

Author

Commented:
It finally ran!! : )  Thanks for the help and patience and information.
Mark EdwardsChief Technology Officer

Commented:
who's buying lunch.....
Mark EdwardsChief Technology Officer

Commented:
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.
ssblueCoordinator

Author

Commented:
Stop by and I will!!  : )  Thanks everyone!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial