Removing Duplicate Records

[Jeff]

I have written a small novel in an attempt to explain the
current database situation in order to receive help in
writing code to automatically remove duplicates from two
tables.

Tables:
X: has two fields:ID#,TermDate
Y: has two fields:ID#,EffectiveDate
Z: has four fields:ID#,TermDate,EffectiveDate,LossDate

Table X and Y are combined into Table Z by running a make-
table query (QueryA)that copies all Table X data and adds
15 days to the TermDate to calculate LossDate, then an
append table query (QueryB)copies all data from Table Y
and adds 180 days to EffectiveDate to calculate LossDate.

A find dup query (QueryC)is ran to check for duplicate
ID#'s in Table Z. If any duplicates are found, they need
to be removed from either Table X or Table Y, depending on
which record has the latest date of point loss.

What I need is code to identify the latest date of point
loss from a set of duplicate ID#'s, identify which table
(X or Y) that the record with the latest date of point
loss came from, then delete that record from the
identified table.

 

[Chris Nebinger]

Air Code coming:
using DAO:

Dim dbs as dao.database
dim rst as dao.recordset

set dbs=currentdb
set rst = dbs.openrecordset("Select
queryC.*,X.TermDate,Y.EffectiveDate from queryc inner join
(x on queryc.id=x.id) INNER JOIN y on queryc.id=y.id")
do until rst.eof
'Duplicate record....delete the earliest:
if EffectiveDate<TermDate then
currentdb.execute "Delete from Y where ID=" & rst!
id
else
currentdb.execute "Delete from Y where ID=" & rst!
id
end if
rst.movenext
loop



No error handling or object cleanup in that snippet.

Create a backup of your database (always do that when you
are deleteing from tables). Then run that snippet. It
should (I think) work the way you want. I hardcoded the
SQL from memory, so if it doesn't work, just create a new
query linking queryc to x and y on the id fields, and use
that as a basis for the recordset.


Chris Nebinger