Deleting records from an "Unmatched" query

G

Guest

Hoping someone can help me resolve a seamingly simple task. I have a query
that finds records in Table1 that do not have matching fields in Table2 and
then I want to delete those records from Table1. I'm obviously a novice but,
I usually get by. Need help this time. Thanks. (btw, running Access 2000 &
2007)
 
J

John Spencer

SImplest is one field in A matches to one field in B

DELETE *
FROM TableA
WHERE TableA.SomeField IN
(SELECT MatchingField FROM TableB)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Guest

Thank you for your response John. I'm almost there. What I actually need is
the inverse of this. I want to delete all records in TableA which has a
particular field that does not match the same field name in TableB. Is this
possible? Thanks again.
 
J

John Spencer

Simplest would be the following

DELETE *
FROM TableA
WHERE TableA.SomeField NOT IN
(SELECT MatchingField FROM TableB)


Simplest does not mean most efficient.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Making a Delete Qiuery from an unmatched Query 3
UnMatched Query 2
Query on two tables with matching null fields 4
Have both records from two queries/tables under one Query without related fields. 0
Updating one table from another table 4
Specify the table containing the records you want to delete 9
Delete Query - with Criteria from SubQuery 4
getting fewer records from a union query than in individual table 5

Top