Delete Unmatched Records

[PA]

Using Access 2003, I have database with a one field table, tblID, with a
series of 3 digit code numbers. Field name is CtxNum. These numbers came
from a mainframe download, imported into Access from a text file.
Another table, tblProvider, has 11 fields, one of which is the code number,
and the field name is likewise CtxNum. I need to delete all records from the
tblProvider where the value in CtxNum doesnt match up with a value in tblID.
I am stumped.
Thanks for any help.

 

[John Spencer]

You can use the unmatched query wizard to build a query that will show all the
records in tblProvider that don't have a match in your tblId table.

Then you can use that query to determine which records to delete.


STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Use a query (SQL view) that looks like the following.

DELETE
FROM TblProvider
WHERE CtxNum in
(SELECT TblId.CtxNum
FROM tblID LEFT JOIN tblProvider
ON tblID.CtxNum = tblProvider.CTXnum
WHERE TblProvider.CtxNum is Null)

OR you can also use

DELETE
FROM TblProvider
WHERE Not Exists
(SELECT *
FROM tblID
WHERE tblID.CtxNum = tblProvider.CTXnum)

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

 

[Duane Hookom]

I would start by creating a select query like:
SELECT tblProvider.*
FROM tblProvider
WHERE CtxNum NOT IN (SELECT CtxNum FROM tblID);

If this looks reasonable, change the query to a delete query and run it.