On Dec 18, 1:14*pm, John Spencer wrote:
Well, if you have imported then into tblPatient then they are in the table.
So an unmatched query is not going to show you anything.

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. *Here are four options - choose your favorite

Option 1:
DELETE
FROM TblPatient
WHERE tblPatient.PatientID in
(SELECT PatientID FROM ImportSourceTable)

Option 2: Optionally if you want to use the backup table instead of the import
table.
DELETE
FROM tblPatient
WHERE tblPatient.PatientID IN
* *(SELECT Temp.PatientID
* * FROM tblPatient as TEMP LEFT JOIN tblPatient_BU
* * ON Temp.PatientID = tblPatient_BU.PatientID
* * WHERE TblPatient_BU.PatientID is NULL)

Option 3:
On the other hand, you might just be able to replace TblPatient with
TblPatient_BU. Unless other changes have been made to the records you want to
retain, this would seem to be the easiest solution.

Option 4:
Another option would be to add a date field (DateCreated) to tblPatient and
set its default to NOW(). *Then all you have to do is identify records that
were created at a certain date and time and delete them.

DELETE
FROM tblPatient
WHERE DateCreated Between #2008-12-18 13:59:00# and #2008-12-18 14:00:00#

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

mcnewsxp wrote:
"Steve" wrote in message
...
Try creating an unmatched query and changing it to a delete query. There's
a wixard for creating an unmatched query by going to queries in the
database window and clicking new.

good idea.
except when i do this no fields show up in the list....???

"mcnewsxp" wrote in message
...
how can i delete records from one table if the records do not exist in a
matching table? *i am making a backup of a table before an import is
performed. *the users may need to undo the backup so i need to be able to
delete only the new records. this is not working, but you get the idea:

DELETE *tblPatient.* FROM tblPatient INNER JOIN tblPatientBackup ON
tblPatient.PatientID = tblPatientBackup.PatientID
WHERE tblPatient.PatientID tblPatientBackup.PatientID;

tia,
mcnewxp

your 3rd option is one that i tried, but i couldn't delete the
tblPatient records because of foreignkey violations.