Comparing date field values and deleting the earlier date record

[Guest]

Hi

I have a table with many duplicate records...I have used the duplicate query
wizard to select the duplicate records....I specified a duplicate as having
the same person and task assigned fields. The other field that I pulled out
into the find duplicates query result is a Date Due field (with dates in
it).....however, I want to go one step further...now that I've selected the
duplicate records into a separate query, I want to compare the date due
fields of the duplicate records and delete the record(s) with the earliest
date due date, thus leaving only the record with the most recent Date Due
date. So I want to compare one duplicate record with another duplicate and
delete the record with the earlier date due date.


Got any ideas how I can do that? Thank you for any hints, suggestions etc.

Regards

Craig

 

[Michel Walsh]

Hi,


That is a little bit difficult since, with Jet, the query is not updateable
if there is an aggregate (MIN and MAX are aggregate) anywhere (except in a
sub query in the WHERE clause).


DELETE b.* FROM tableName As b
WHERE b.dateTimeStamp <> ( SELECT MAX(a.dateTimeStamp)
FROM tablename As a
WHERE a.person=b.person AND
a.taskID=b.taskID)



... and try only on a dummy set of data, in case something goes wrong, to
avoid loosing data.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you very much! I will try to implement your suggestion