Well, if the data in tblPatient is linked to other tables then you will have to
go into the relationships window
, delete the relationship links involving tblPatient
, save the changes
, then replace (or rename) tblPatient with tblPatient_BU. (I suggest you
rename tblPatient to tblPatient_Original and tblPatient_BU to tblPatient)
, and then re-establish the relationships.

Test things and if all seems to be working correctly, you can then rename
tblPatient_Original to tblPatient_BU or delete tblPatient_Original.


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

mcnews wrote:
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.