deleteQry delete duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am importing data from one table to another. There is a potential for
duplicates that I am trying to eliminate using a delete query. The
tblAppointments has the following fields an appointmentID (autonumber and
keyfield), clientID(not unique), and AppointmentDate.

tblUpdateAppointments is used to update tblAppointments using an append
query. But before I want the append query to run, I would like to remove
records that have the same clientID AND AppointmentDATE because these are
duplicates. I have written the following delete query but it will not run and
I'm not sure why. Any help would be much appreciated.

DELETE tblUpdateAppointments.*, tblUpdateAppointments.[clientID],
tblUpdateAppointments.[AppointmentDate]
FROM tblUpdateAppointments, tblAppointments
WHERE (((tblUpdateAppointments.[clientID])=[tblAppointments].[clientID]) AND
((tblUpdateAppointments.[AppointmentDate])=[tblAppointments].[AppointmentDate]));
 
I am importing data from one table to another. There is a potential for
duplicates that I am trying to eliminate using a delete query. The
tblAppointments has the following fields an appointmentID (autonumber and
keyfield), clientID(not unique), and AppointmentDate.

tblUpdateAppointments is used to update tblAppointments using an append
query. But before I want the append query to run, I would like to remove
records that have the same clientID AND AppointmentDATE because these are
duplicates. I have written the following delete query but it will not run and
I'm not sure why. Any help would be much appreciated.
Click to expand...

I'd suggest simply not appending them at all, in the first place! Left
join your Append query to the target table on ClientID and
AppointmentDate, and use a criterion of IS NULL on the
tblAppointments. This will ensure that only new records get appended.

Or, put a unique two-field Index on those two fields; the duplicates
will cause a warning message but will not be appended.

Always better to prevent erroneous records from being added in the
first place than to add them and (try to) delete them later!

John W. Vinson[MVP]
 
Back
Top